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 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
– JNevill, Commented Jun 23, 2021 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
– dros, Commented Jun 23, 2021 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
– JNevill, Commented Jun 23, 2021 at 15:50
im not too sure what you mean, could you provide an example? — dros
– dros, Commented Jun 23, 2021 at 16:01

Laurenz Albe · Accepted Answer · 2021-06-23 15:30:23Z

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, 2021 at 15:30

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

dros Over a year ago

apologies, bit of incorrect copy and past there. fixed with correct columbs added

Laurenz Albe Over a year ago

Ok, makes sense now. Just use the actual column names instead of the ones in my answer then.

Chris Kobrzak · Accepted Answer · 2022-10-13 21:51:30Z

1

The MERGE command is available as of Postgres 15.

MERGE documentation

answered Oct 13, 2022 at 21:51

Chris Kobrzak

1,5901 gold badge18 silver badges26 bronze badges

Collectives™ on Stack Overflow

SQL Server MERGE statement in Postgresql?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related