Merge in postgres

Question

Am trying to convert below oracle query to postgres,

MERGE INTO table1 g
USING (SELECT distinct g.CDD , d.SGR
from  table2 g, table3 d
where g.IDF = d.IDF) f
ON (g.SGR = f.SGR and g.CDD = f.CDD)
   WHEN NOT MATCHED THEN
   INSERT (SGR, CDD)
   VALUES (f.SGR, f.CDD);

I made changes as below compatible to postgres:

WITH f AS (
SELECT distinct g.CDD , d.SGR
from  table2 g, table3 d
where g.IDF = d.IDF
),
upd AS (
update table1 g 
set 
SGR = f.SGR , CDD = f.CDD 
FROM f where g.SGR = f.SGR  and g.CDD = f.CDD  
returning g.CDD, g.SGR
)
INSERT INTO table1(SGR, CDD ) SELECT f.SGR, f.CDD FROM f;

But am doubtful ,my oracle query is not updating any columns if data matched , but am unable to convert it accordingly . Can anyone help me to correct it ?

If you don't want to update the target table, then why do you have an UPDATE in your migrated statement? — user330315
– user330315, Commented Sep 25, 2018 at 12:39
Unrelated, but: if you start with a new project today, you should be using Postgres 10, not 9.5 (it's like starting a new project with an outdated Oracle 11 today) — user330315
– user330315, Commented Sep 25, 2018 at 12:40
@JavierLarroulet :: Am not getting any error message , as i have used update table its changing the output values . Update should be removed and implemented same as oracle logic — Syeda samreen anjum
– Syeda samreen anjum, Commented Sep 26, 2018 at 5:49
@a_horse_with_no_name : our project requirement is 9.5 . though outdated ,unwillingly we have to use this one .I was unable to convert it without update in migrated code ..Can you suggest how can i convert this oracle to postgres query without updating my table ? — Syeda samreen anjum
– Syeda samreen anjum, Commented Sep 26, 2018 at 5:52

score 3 · Accepted Answer · 2018-09-26 09:10:10Z

3

Assuming you have a primary (or unique) key on (sgr, cdd) you can convert this to an insert ... on conflict statement:

insert into table1 (SGR, CDD)
select  distinct g.CDD, d.SGR
from table2 g
  join table3 d ON g.IDF = d.IDF
on conflict (cdd, sgr) do nothing;

If you don't have a unique constraint (which bears the question: why?) then a straight-forward INSERT ... SELECT statement should work (which would have worke in Oracle as well).

WITH f AS (
   SELECT distinct g.CDD, d.SGR
   from table2 g
     join table3 d on g.IDF = d.IDF
)
INSERT INTO table1 (SGR, CDD) 
SELECT f.SGR, f.CDD 
FROM f
WHERE NOT EXISTS (select *
                  from table1 t1
                     join f on (t1.sgr, t1.cdd) = (f.cdd, f.sgrf));

Note that this is NOT safe for concurrent execution (and neither is Oracle's MERGE statement). You can still wind up with duplicate values in table1 (with regards to the combination of (sgr,cdd)).

The only sensible way to prevent duplicates is to create a unique index (or constraint) - which would enable you to use the much more efficient insert on conflict. You should really consider that if your business rules disallow duplicates.

Note that I converted your ancient, implicit join in the WHERE clause to a modern, explicit JOIN operator, but it is not required for this to work.

edited Sep 26, 2018 at 9:10

answered Sep 25, 2018 at 12:38

user330315

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

3 Comments

Syeda samreen anjum Over a year ago

Am getting below error : ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification SQL state: 42P10

user330315 Over a year ago

Then you don't have a unique constraint on (cdd, sgr) - why not?

Syeda samreen anjum Over a year ago

Table shouldn't be modified , Is there any other approach apart from this ?

Ali-Alrabi · Accepted Answer · 2023-03-18 11:08:40Z

2

New Postgres version 15 now support merge statement, You don't need to use alternate cte

Here is an example

MERGE into b using a on a.id = b.id
when matched then 
  update set x = b.x + 1
when not matched then 
  insert (id,x,status) values (a.id,a.x,a.status);

answered Mar 18, 2023 at 11:08

Ali-Alrabi

1,7506 gold badges30 silver badges62 bronze badges

Collectives™ on Stack Overflow

Merge in postgres

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related