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 🙂

20 Comments

  1. 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);

  2. 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.

  3. 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?

  4. 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?

  5. 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!

  6. 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

    1. 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

  7. 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

    1. 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);

  8. 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);

    1. 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);
      
  9. Note that this technique is still useful, even with PG’s “official” upsert support “INSERT … ON CONFLICT DO UPDATE … ” SQL, e.g., if the target mytable2 doesn’t have a primary key or unique index on PID, the official “INSERT … ON CONFLICT DO UPDATE” will fail because equi-preds don’t “cover” a unique constraint or primary key columns. The technique here still works. Very nice, thanks!

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