How to perform a MERGE (insert/update/delete) using PostgreSQL?

Question

I'm using INSERT ... ON CONFLICT ... to be able to upsert some data in a PostgreSQL table.

But now I'd also like to be able to delete some existing rows, if they are not provided by the current INSERT query. So, in addition to INSERT and UPDATE, I would like to be able to do a DELETE.

Using SQL Server, I would do this using a MERGE query and :

WHEN NOT MATCHED BY SOURCE THEN DELETE

What is the recommended way to achieve something similar using PostgreSQL?

I would prefere not to run two separated queries.

We can't do that yet :^( Wait for PostgreSQL v12 which will probably have a MERGE statement... — Laurenz Albe
– Laurenz Albe, Commented Jul 30, 2018 at 16:02
So how do you workaround it using PostgreSQL? I mean, it's not such an edge case... Two requests are mandatory? — electrotype
– electrotype, Commented Jul 30, 2018 at 16:32

Thomas Berger · Accepted Answer · 2018-07-30 21:32:57Z

8

You could use a CTE for that

WITH updated AS (
INSERT ... 
INTO tbl 
ON CONFLICT ...
RETURNING your_primary_key
)
DELETE FROM tbl t 
WHERE your_primary_key NOT IN ( 
  SELECT updated.your_primary_key FROM updated
);

answered Jul 30, 2018 at 21:32

Thomas Berger

1,87013 silver badges26 bronze badges

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

1 Comment

YTerle Over a year ago

could you please create an example of the above? I'm not able to update values which already have an id (serial primary key) and insert values which do not exist in table.

Collectives™ on Stack Overflow

How to perform a MERGE (insert/update/delete) using PostgreSQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related