On 30th of March 2024, Dean Rasheed committed patch:
Add support for MERGE ... WHEN NOT MATCHED BY SOURCE. This allows MERGE commands to include WHEN NOT MATCHED BY SOURCE actions, which operate on rows that exist in the target relation, but not in the data source. These actions can execute UPDATE, DELETE, or DO NOTHING sub-commands. This is in contrast to already-supported WHEN NOT MATCHED actions, which operate on rows that exist in the data source, but not in the target relation. To make this distinction clearer, such actions may now be written as WHEN NOT MATCHED BY TARGET. Writing WHEN NOT MATCHED without specifying BY SOURCE or BY TARGET is equivalent to writing WHEN NOT MATCHED BY TARGET. Dean Rasheed, reviewed by Alvaro Herrera, Ted Yu and Vik Fearing. Discussion: https://postgr.es/m/CAEZATCWqnKGc57Y_JanUBHQXNKcXd7r=0R4NEZUVwP+syRkWbA@mail.gmail.com
This looks pretty cool. So, since Pg 15 we have MERGE command.
It is used to insert, or delete, or update set of rows based on some conditions, all as single command.
Now, we got one very, very cool addition.
Let's setup some test:
=$ CREATE TABLE base_data ( id int8 generated always AS IDENTITY PRIMARY KEY, codename text NOT NULL UNIQUE, val int4 NOT NULL DEFAULT 0 ); CREATE TABLE =$ INSERT INTO base_data (codename, val) SELECT x, LENGTH(x) * 10 FROM unnest('{yep,duel,young,manias}'::text[]) x; INSERT 0 4 =$ SELECT * FROM base_data; id | codename | val ----+----------+----- 1 | yep | 30 2 | duel | 40 3 | young | 50 4 | manias | 60 (4 ROWS) =$ CREATE TABLE incr AS SELECT codename, FLOOR(random() * 3) - 1 AS diff FROM base_data; SELECT =$ INSERT INTO incr (codename, diff) VALUES ('depesz', 15 ); INSERT 0 1 =$ SELECT * FROM incr; codename | diff ----------+------ yep | -1 duel | 1 young | 0 manias | 0 depesz | 15 (5 ROWS)
Now, with MERGE we can add all of the things from incr to base_data, updating val:
=$ MERGE INTO base_data AS b USING incr AS i ON b.codename = i.codename WHEN MATCHED THEN UPDATE SET val = b.val + i.diff WHEN NOT MATCHED THEN INSERT (codename, val) VALUES (i.codename, i.diff) ; MERGE 5 =$ SELECT * FROM base_data; id | codename | val ----+----------+----- 1 | yep | 29 2 | duel | 41 3 | young | 50 4 | manias | 60 7 | depesz | 15 (5 ROWS)
As you can see all diffs were correctly applied. The new thing, though, is pretty cool.
Let's remove one row from diff:
=$ DELETE FROM incr WHERE codename = 'duel'; DELETE 1
And now, I'll run modified merge:
=$ MERGE INTO base_data AS b USING incr AS i ON b.codename = i.codename WHEN MATCHED THEN UPDATE SET val = b.val + i.diff WHEN NOT MATCHED THEN INSERT (codename, val) VALUES (i.codename, i.diff) WHEN NOT MATCHED BY SOURCE THEN DELETE ; MERGE 5 =$ SELECT * FROM base_data; id | codename | val ----+----------+----- 1 | yep | 28 3 | young | 50 4 | manias | 60 7 | depesz | 30 (4 ROWS)
Please note that I deleted duel from incr, and the merge deleted it from base_data. The magic is the WHEN NOT MATCHED BY SOURCE clause, which means that if there is row in table we're merging to that doesn't exist in source (table what we're USING) – we can do whatever we need with the row in base_data.
This is absolutely awesome, and will make all kinds of data loading/updating processes simpler.
Thanks a lot to everyone that worked on it.