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.

Answer 1

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
);