UPSERT/MERGE using Writable CTE in PostgreSQL 9.1

26 Oct

There is always discussion of having UPSERT/MERGE in postgresql. Since, oracle and other RDBMS has this feature, therefore people ask about this feature in PostgeSQL.

In previous version of PostgreSQL, we used to implement it using functions. Now in PostgreSQL 9.1, user can implement this feature using Writable CTE.

PostgreSQL 9.1, now has Writable CTE. WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can also be a SELECT, INSERT, UPDATE, or DELETE.

Lets see how we can use Writable CTE for UPSERT.

Following are SQL Code which can be use in Oracle and PostgreSQL for creating sample data:

For oracle:

create table myTable
  (pid number, sales number, status varchar2(6));

create table myTable2
  (pid number, sales number, status varchar2(6));

insert into myTable2 values(1,12,'CURR');
insert into myTable2 values(2,13,'NEW' );
insert into myTable2 values(3,15,'CURR');
insert into myTable  values(2,24,'CURR');
insert into myTable  values(3, 0,'OBS' );
insert into myTable  values(4,42,'CURR');
select * from myTable2;

For PostgreSQL 9.1:

create table myTable
  (pid numeric, sales numeric, status varchar(6));

create table myTable2
  (pid numeric, sales numeric, status varchar(6));

insert into myTable2 values(1,12,'CURR');
insert into myTable2 values(2,13,'NEW' );
insert into myTable2 values(3,15,'CURR');
insert into myTable  values(2,24,'CURR');
insert into myTable  values(3, 0,'OBS' );
insert into myTable  values(4,42,'CURR');
select * from myTable2;

In Oracle, people use Merge Something like given below:

merge into myTable2 m
         using myTable d
          on (m.pid = d.pid)
   when  matched
   then  update set   m.sales  = m.sales+d.sales
                ,     m.status = d.status
   when  not matched
   then  insert values (d.pid,d.sales,'NEW');

SQL> select * from myTable2;
       PID	SALES STATUS
---------- ---------- ------
	 1	   12 CURR
	 2	   37 CURR
	 3	   15 OBS
	 4	   42 NEW

In PostgreSQL 9.1, with writable CTE:

WITH upsert as
(update mytable2 m set sales=m.sales+d.sales, status=d.status from mytable d where m.pid=d.pid
  RETURNING m.*
)
insert into mytable2 select a.pid, a.sales,'NEW' from mytable a where a.pid not in (select b.pid from upsert b);

postgres=# select * from mytable2 order by 1;
 pid | sales | status
-----+-------+--------
   1 |    12 | CURR
   2 |    37 | CURR
   3 |    15 | OBS
   4 |    42 | NEW

Now, we have Writable CTE which can help us make UPSERT in PostgreSQL.
Enjoy :)

19 Responses to “UPSERT/MERGE using Writable CTE in PostgreSQL 9.1”

  1. Delores November 15, 2011 at 20:46 #

    This is a excellent site.

  2. lukaseder November 22, 2011 at 14:27 #

    That’s a pretty crazy approach! Very nice though! I might think about simulating the SQL:2003 standard MERGE clause using this approach for Postgres in jOOQ (http://www.jooq.org)

    PS: I think your line 5 has a mistake. You might have meant to write
    not in (select b.pid from upsert b);

  3. Theo Tisor November 29, 2011 at 02:11 #

    never would have known

  4. arthurmelissen March 29, 2012 at 13:41 #

    Interesting, but does this have any advantages over implementing UPSERT through a RULE with ON INSERT … DO INSTEAD UPDATE?

    The RULE based approach has the upside that you do not have to modify your INSERT statement, but I’m not sure about performance and other aspects.

    • Database Technologies May 30, 2012 at 16:01 #

      Thats true rule based can be use. However, Performance wise it is faster over creating rule for each insert on table when you have to merge large amount of data into table.

  5. Josh Nankivel October 29, 2012 at 20:34 #

    What kind of record locking occurs on the destination side with this approach – if users on the destination try to run a query, do their queries get frozen until this operation completes?

    • Database Technologies October 29, 2012 at 21:35 #

      Well, in my example its going to ROW level exclusive locks for rows which going to be updated. And if row level exclusive lock has already been done, then query after this, will be in waiting state.

  6. Manuel Rodriguez November 29, 2012 at 16:03 #

    Insert with “where” clause? I’m trying to do something like this and it doesn’t work!!
    Is there any alternative to do something like this but without a rule?

  7. Bert December 3, 2012 at 07:04 #

    Getting upsert to work with multiple pk’s was a bit of a struggle for me, since I’m totally new to the concept, but I was used to the merge syntax. But I got it to work!
    Mostly thanks to this blog post!

  8. Ajit Verma December 28, 2012 at 20:42 #

    Hi, i am newbie to postgres and i am trying to follow your example to accomplish an insert if the update wasn’t successful. My query is not working.
    I am trying something like this -

    WITH upsert as
    (update mytable m set m.sales=50, m.status= ‘NOW’ where m.pid= 2
    RETURNING m.pid
    )
    insert into mytable (pid, sales, status) values (2, 50, ‘NOW’) WHERE (select * from upsert) is null;

    Your advice will be extremely helpful

    • Database Technologies January 7, 2013 at 15:55 #

      Hi Ajit,

      I think you are using wrong statement in INSERT.
      INSERT can’t have WHERE clause as you have shown me. For WHERE clause with INSERT, you would like to use Subquery.
      Second I am not sure, if I understood your correct requirement. I think it will be good idea if you can explain more about your requirement.

      -Vibhor

  9. Alex Kleyman January 8, 2013 at 16:16 #

    Trying to translate a merge from DB2 to PostgreSQL
    First part of PostgreSQL upsert

    WITH upsert as

    (update textb_2013_149100900000.ORDERLINES NEW_ORDER
    set QUANTITY = OLD_ORDER.QUANTITY + NEW_ORDER.QUANTITY,
    UPDATEUSER = ‘vasya’
    from (SELECT * FROM textb_2013_149100900000.ORDERLINES WHERE ORDERID = 2590) OLD_ORDER
    where NEW_ORDER.RAWISBN = OLD_ORDER.RAWISBN AND NEW_ORDER.ORDERID = 2584
    RETURNING NEW_ORDER.*
    )

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++

    Having a problem translating the insert part when RAWISBN is not found in the upsert

    • Database Technologies January 9, 2013 at 15:57 #

      You can try something like given below:

      WITH UPSERT as
      (UPDATE ORDERLINES NEW_ORDER SET NEW_ORDER.QUANTITY = NEW_ORDER.QUANTITY + OLD_ORDER.QUANTITY, UPDATEUSER =’newuser’ FROM ORDERLINES OLD_ORDER WHERE OLD_ORDER.RAWISBN=NEW_ORDER.RAWISBN and OLD_ORDER.ORDERID=2590 and NEW_ORDER.ORDERID=2584
      RETURNING NEW_ORDER.*
      )
      INSERT INTO ORDERLINES(ORDERID, ISBN, TITLE, AUTHOR, QUANTITY, PRICE, VERIFIED, UPDATEUSER) SELECT 2584,
      OLD_ORDER.ISBN,
      OLD_ORDER.TITLE,
      OLD_ORDER.AUTHOR,
      OLD_ORDER.QUANTITY,
      OLD_ORDER.PRICE,
      OLD_ORDER.VERIFIED,
      ‘newuser’
      FROM ORDERLINES OLD_ORDER WHERE OLD_ORDER.ORDERID=2590 and OLD_ORDER.RAWISBN not in (SELECT RAWISBN FROM upsert);

  10. Ashwin Jayaprakash February 16, 2013 at 01:12 #

    Thanks! Very useful.

    Do you think the query could be rewritten a little to use NOT EXISTS instead of NOT IN:

    with ( .. .. returning m.*)
    insert into mytable2
    select a.pid, a.sales,’NEW’
    from mytable a where not exists (select 1 from upsert b);

    • Database Technologies February 18, 2013 at 21:59 #

      Yes. It can be re-write using Not Exists. Something like given below:

      WITH upsert as
      (update mytable2 m set sales=m.sales+d.sales, status=d.status from mytable d where m.pid=d.pid
        RETURNING m.*
      )
      insert into mytable2 select a.pid, a.sales,'NEW' from mytable a where NOT EXISTS (select 1 from upsert b where b.pid=a.pid);
      

Trackbacks/Pingbacks

  1. Database Technologies - December 16, 2011

    [...] 4. Updateable Views using INSTEADOF Trigger in PostgreSQL 9.1 5. Utility Operation Improvements 6. Upsert/Merge using Writeable CTE 7. New Functions/Improvement 8. Object Functions Improvement in PostgreSQL 9.1 9. Client [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 25 other followers

%d bloggers like this: