Migrating an Oracle MERGE statement to a PostgreSQL UPSERT statement

Question

Can you please help me converting the following Oracle MERGE statement into a valid UPSERT statement for use in a PostgreSQL 9.3 database?

MERGE INTO my_table a
     USING (SELECT v_c1 key,
                   v_c2 AS pkey,
                   v_c3 AS wcount,
                   v_c4 AS dcount
              FROM DUAL) b
        ON (    a.key = b.key
            AND a.pkey = b.pkey
WHEN MATCHED
THEN
   UPDATE SET wcount = b.wcount,
              dcount = b.dcount
WHEN NOT MATCHED
THEN
   INSERT     (key,
               pkey,
               wcount,
               dcount)
    VALUES(b.key,b.pkey,b.wcount,b.dcount);

Community · Accepted Answer · 2017-05-23 12:04:04Z

4

I don't think there's UPSERT statement in PostgreSQL 9.3, but you can do this:

with cte_dual as (
    select
        v_c1 as key,
        v_c2 as pkey,
        v_c3 as wcount,
        v_c4 as dcount
), cte_update as (
    update my_table as a set
        wcount = b.wcount,
        dcount = b.dcount
    from cte_dual as b
    where b.key = a.key and b.pkey = a.pkey
    returning *
)
insert into my_table (key, pkey, wcount, dcount)
select d.key, d.pkey, d.wcount, d.dcount
from cte_dual as d
where not exists (select * from cte_update as u WHERE u.key = d.key and u.pkey = d.pkey)

You can read couple of similar questions:

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Oct 16, 2013 at 8:06

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gpa Over a year ago

it complains about ERROR: relation "dual" does not exist Position: 301

1737973 · Accepted Answer · 2017-10-16 16:59:27Z

1

I ran on this today, read these, the docs, eventually used something very similar to what to my understanding the docs are advising now for UPSERT:

INSERT INTO table (
    $INSERT_COLUMN_NAMES
)
VALUES (
    $INSERT_COLUMN_VALUES
)
ON CONFLICT (
    $LIST_OF_PRIMARY_KEY_COLUMN_NAMES
)
DO UPDATE SET
    $UPDATE_SET_SUBCLAUSE
WHERE 
    $WHERE_CONDITION_ON_PRIMARY_KEYS
;

I was actually working it at DAO level so I can not provide a meaningful example at the moment, will review this later.

If I'm not mistaken is working perfectly fine. I felt forced into create a primary key that wasn't there, but actually belonged there.

answered Oct 16, 2017 at 16:59

1737973

1581 gold badge20 silver badges47 bronze badges

Comments

Mike Laren · Accepted Answer · 2015-07-27 06:02:43Z

0

upsert works in Postgres 9.3 ,

Example-

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 mytable2 b);

edited Jul 27, 2015 at 6:02

Mike Laren

8,23618 gold badges53 silver badges72 bronze badges

answered Jul 27, 2015 at 5:59

saket kumar naidu

1

Collectives™ on Stack Overflow

Migrating an Oracle MERGE statement to a PostgreSQL UPSERT statement

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related