MERGE had been discussed on the PostgreSQL mailing list for years before it landed in core with PostgreSQL 15. The command combines insert, update and delete logic into a single statement whose behavior is governed by explicit match rules.
Preparing a target table
A target table is built from two columns, the second holding ten times the value of the first.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
db15=# CREATE TABLE t_test ( id serial PRIMARY KEY, val int ); CREATE TABLE db15=# INSERT INTO t_test (val) SELECT x * 10 FROM generate_series(1, 10) AS x; INSERT 0 10 db15=# SELECT * FROM t_test; id | val ---+----- 1 | 10 2 | 20 3 | 30 4 | 40 5 | 50 6 | 60 7 | 70 8 | 80 9 | 90 10 | 100 (10 rows) |
This table receives the changes applied by the operations that follow.
Merging a source into the target
The USING clause produces a source relation, y, which is merged into t_test as the target. The WHEN clauses define what happens on a match and on its absence: matching rows are overwritten, missing rows are inserted.
|
1 2 3 4 5 6 7 8 9 10 |
db15=# MERGE INTO t_test USING (SELECT x, random() * 1000 AS z FROM generate_series(1, 16, 2) AS x ) AS y ON t_test.id = y.x WHEN MATCHED THEN UPDATE SET val = z WHEN NOT MATCHED THEN INSERT (val) VALUES (z); MERGE 8 |
Two details stand out. The UPDATE statement no longer needs the target table to be named, and the same holds for the trailing INSERT — the table is not repeated there either.
The output reflects those rules: all odd numbers were updated, the remaining rows were left untouched, and the missing rows were added.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
db15=# SELECT * FROM t_test ORDER BY id; id | val ---+----- 1 | 101 2 | 20 3 | 656 4 | 40 5 | 309 6 | 60 7 | 897 8 | 80 9 | 195 10 | 100 11 | 634 12 | 625 13 | 50 (13 rows) |
Deleting through MERGE
DELETE is also permitted inside the statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
db15=# MERGE INTO t_test USING (SELECT x, random() * 1000 AS z FROM generate_series(1, 16, 2) AS x) AS y ON t_test.id = y.x WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT (val) VALUES (z); MERGE 8 db15=# SELECT * FROM t_test ORDER BY id; id | val ---+----- 2 | 20 4 | 40 6 | 60 8 | 80 10 | 100 12 | 625 14 | 648 (7 rows) |
Here every matching row is removed. The DELETE takes no further arguments — which rows are affected is unambiguous, so no column information is required.
For PostgreSQL 15, MERGE has been a long-awaited addition, and it simplifies a substantial amount of surrounding code.
