It’s been many years since the first ideas to implement the MERGE command surfaced on the PostgreSQL mailing list. Many years later, this important feature has finally made it into PostgreSQL core, and will (as of today) make it into PostgreSQL 15. To show people how this vital command works, I have decided to come up with a technical preview to introduce my readers to this wonderful new command. It adds so much power to PostgreSQL.
MERGE: Preparing a sample table
As the name already suggests,
MERGE can be used to merge lists and to combine them into one. It allows you to define the behavior, opens an easy way to import missing data and a lot more.
Let’s prepare some data and try it out:
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)
What we’ve produced is a list consisting of two columns. The second value is 10 times the value in the first column. This table will be our “target table” which is supposed to receive changes.
Using MERGE in PostgreSQL 15
Let’s try to run
MERGE on our data set and see what happens. Here’s an example of a simple query:
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
t_test table will be the target table and we merge “y”, which is the result of the
USING clause, into it. The
WHEN clauses simply define the rules applied during the
MERGE operation. When we have a match, we want to overwrite the existing value. In case the value does not exist we want to run
INSERT. There are two things worth mentioning here: If we look closely at the
UPDATE statement, we see that the target table is not needed anymore. That’s also true for the
INSERT statement at the end – there is no need to define the table all over again.
The result is as expected:
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)
What we see here is that all the odd numbers have been updated – the rest was unchanged. Missing rows were added.
However, there is more: We can also use
DELETE inside of a
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)
In this case we have deleted all matching rows. The
DELETE does not need any further arguments. It is totally clear which rows are affected and column information is not needed.
MERGE is a valuable new feature in PostgreSQL 15. We’ve waited for many years and now it makes a lot of code much easier to deal with.
If you want to learn more about PostgreSQL and if you’re interested in how PostgreSQL stores functions and procedures, check out our post about it.