CODEWITHSUNDEEP
sqlpostgresqlupsertsql-merge
dros
dros

Reputation: 1557

SQL Server MERGE statement in Postgresql?

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)

Upvotes: 0

Views: 1419

Answers (2)

Chris Kobrzak
Chris Kobrzak

Reputation: 1392

The MERGE command is available as of Postgres 15.

MERGE documentation

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246268

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!

Upvotes: 1

Related Questions