How to correct migrate MERGE statement with " NOT MATCHED BY TARGET " from MS SQL to PostgreSQL?

Question

I have such sql statement:

MERGE pvl.testTable AS T
USING temp.testTable AS S
ON (T.Id = S.ID)
WHEN NOT MATCHED BY TARGET THEN
  INSERT (first,
          second,
          third,
          fourth) VALUES (s.first,
                          s.second,
                          s.third,
                          s.fourth)
WHEN MATCHED
THEN
  UPDATE
  SET
    T.first  = S.first,
    T.second = S.second,
    T.third  = S.third,
    T.fourth = S.fourth
WHEN NOT MATCHED BY SOURCE
THEN
  DELETE;

Also I know that I must use ON CONFLICT, but how i can deal with WHEN NOT MATCHED BY TARGET and WHEN NOT MATCHED BY SOURCE?

Answer 1

You could do it in two steps: 1. Upsert and 2. Delete

-- Perform upsert and return all inserted/updated ids
WITH upsert(id) AS
(
INSERT INTO target_table (first, second, third, fourth)
SELECT first, second, third, fourth FROM source_table
ON CONFLICT (id) DO UPDATE SET
  first = excluded.first,
  second = excluded.second,
  third = excluded.third,
  fourth = excluded.fourth
RETURNING id
)

-- Delete any records in target that aren't in source table
DELETE FROM target_table
WHERE id NOT IN (SELECT id FROM upsert);