It's been many years since the first ideas to implement the MERGE command surfaced on the PostgreSQL mailing list. Now this important feature has finally made it into PostgreSQL core, in 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 command. It adds so much power to PostgreSQL.
Table of Contents
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.
|
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) |
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.
Let's try to run MERGE on our data set and see what happens. Here's an example of a simple query:
|
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 |
The 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.
|
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) |
What we see here is that all the odd numbers have been updated - the rest was unchanged. Missing rows were added.
DELETE inside of a MERGE 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) |
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.
+43 (0) 2622 93022-0
office@cybertec.at
Is the "WHEN NOT MATCHED BY SOURCE" clause is going to be supported? It's crucial for ETLs to mark records in data warehouse as deleted if they're hard deleted in source table. Thanks.
I'm not quite getting it... Shouldn't
generate_series(1, 16, 2)
Make a series 1, 3, 5, ... 11, 13, 15?
So why do your examples top out at id = 13; isn't a row with id = 15 missing?
It is a great feature and I'm looking forward to try it out. Thanks for introducing it.