PostgreSQL version 15 introduces a new SQL command: the MERGE command. This allows merging a table into another table. The MERGE command has existed for some time in other databases such as Oracle or SQL Server.
The principle of this command is that you have a target table in which you want to insert or remove data based on a source table under certain conditions, or you want to update existing entries with data from the source table. The source table doesn’t have to be a real table, it can just as easily be a SELECT query.
How to use it, step-by-step
The command begins with MERGE INTO, followed by the name of the target table. We call it
MERGE INTO dest ...
Then you specify the source table with USING, here we call it
MERGE INTO dest USING src ...
If you want to use a SELECT query as the source instead of a real table, you can do it like this:
MERGE INTO dest USING (SELECT ... FROM ...) AS src ...
Now you need a condition that is used to match entries from one table to entries from the other table. This is specified after ON. In this example we simply use the IDs of the two tables:
MERGE INTO dest USING src ON dest.id=src.id ...
This is followed by a case distinction that describes what should happen if the condition either applies or not. The possible actions can be: UPDATE, DELETE, INSERT, or DO NOTHING.
The two cases are specified with WHEN MATCHED THEN and WHEN NOT MATCHED THEN:
MERGE INTO dest USING src ON dest.id=src.id WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT (...) VALUES (...);
If a match exists, then reasonable actions are UPDATE, DELETE, or DO NOTHING. If no match exists, then reasonable actions are INSERT or DO NOTHING.
In the WHEN cases, additional conditions can be specified with AND:
MERGE INTO dest USING src ON dest.id=src.id WHEN MATCHED AND dest.value > src.value THEN DELETE WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN DO NOTHING;
A realistic example
Here’s an example demonstrating a use case that might occur in the real world:
MERGE INTO account a USING transaction t ON a.id=t.account_id WHEN MATCHED THEN UPDATE SET balance = a.balance + t.amount WHEN NOT MATCHED THEN INSERT (id, balance) VALUES (t.account_id, t.amount);
This statement processes a table of monetary transactions and applies them to their matching customer accounts by adding the amount of each transaction to the balance of the matching account. If no matching account exists it will be created and the initial balance is the amount of the first transaction.