SQL Server MERGE statement in Postgresql?

Question

merge into Mytable WITH (HOLDLOCK) as dst
                    using (select @mId as MId,  
                        @up as UP, 
                        @termsAccepted as TermsAccepted, 
                        @AnalyticsAccepted as AnalyticsAccepted, 
                        @EssentialCookiesPolicyWasAccepted as EssentialAccepted,

                    ) as src on dst.MId = src.MId and dst.UP = src.UP
                when matched then 
                    update set dst.TermsAccepted = src.TermsAccepted,
                        dst.AnalyticsAccepted = src.AnalyticsAccepted,
                        dst.EssentialAccepted = src.EssentialAccepted,

                when not matched then
                    insert(MId, UP ) 
                    values(src.MId, src.UP)

Trying to convert this into postgres but cant seem to find a reason syntax replacement for merge?

tried a recursive query but slightly lost on how deep to go?

WITH f AS (
select mId as MId,  
up as UP, 
) as src on dst.MId = src.MId and dst.UP = src.UP
)
INSERT INTO myTable (MId, UP) 
SELECT src.MId, src.UP
FROM src
WHERE NOT EXISTS (
insert(MId, UP ) 
values(src.MId, src.UP)

Good answer [here](https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql) on how Postgres 9.5+ "INSERT ON CONFLICT (key) DO UPDATE...` Insert syntax that should work as a replacement to `MERGE` in sql server. — JNevill, Jun 23 '21 at 14:48
thanks, I have used insert into but I cannot seem to understand the recursion part of it. still working o nit — dros, Jun 23 '21 at 15:14
I don't think there is any need for recursion here. You are upserting/merging from one table/result-set into another table based on a key. Whether thats `UPSERT`, `MERGE`, `INSERT INTO ... ON DUPLICATE KEY...` `INSERT INTO ... ON CONFLICT...` it's more or less a sql syntax question. — JNevill, Jun 23 '21 at 15:50
im not too sure what you mean, could you provide an example? — dros, Jun 23 '21 at 16:01

score 1 · Answer 1 · answered Jun 23 '21 at 15:30

1

Not sure what the Microsoft SQL server statement does exactly, but you can try something like:

INSERT INTO mytable (mid, up) VALUES ($1, $2)
ON CONFLICT (mid, up)
DO UPDATE SET mid = EXCLUDED.mid, up = EXCLUDED.up;

This requires a unique constraint on (mid, up).

The columns in the UPDATE branch in your question have different names, but that doesn't make any sense to me. Replace details as necessary!

answered Jun 23 '21 at 15:30

Laurenz Albe

209,280
17
206
263

1

apologies, bit of incorrect copy and past there. fixed with correct columbs added – dros Jun 23 '21 at 15:36
Ok, makes sense now. Just use the actual column names instead of the ones in my answer then. – Laurenz Albe Jun 23 '21 at 19:26

score 0 · Answer 2 · answered Oct 13 '22 at 21:51

0

The MERGE command is available as of Postgres 15.

MERGE documentation

answered Oct 13 '22 at 21:51

Chris Kobrzak

1,044
14
20

SQL Server MERGE statement in Postgresql?

2 Answers2