PostgreSQL 15: Using MERGE in SQL

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.

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

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.

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 MERGE statement:

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.

Finally …

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.