3

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?

Roman Martyshchuk
  • 195
  • 1
  • 2
  • 7

1 Answers1

5

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);
Community
  • 1
  • 1
Nick
  • 7,103
  • 2
  • 21
  • 43
  • It's a shame that Postgres doesn't have a fuller-featured MERGE command like MSSQL, which would allow this to all be combined into a single, clearer query. – Stephen Jan 31 '20 at 21:15