MERGE

An SQL command for incorporating rows from one relation into another

MERGE is a DML command for conditionally incorporating rows from one relation into another relation.

MERGE was added in PostgreSQL 15.

Change history

Examples

Basic, somewhat contrived usage example for MERGE:

postgres=# CREATE TABLE foo (id INT NOT NULL PRIMARY KEY, val TEXT NOT NULL);
CREATE TABLE

postgres=# CREATE TABLE bar (id INT NOT NULL PRIMARY KEY, val TEXT NOT NULL);
CREATE TABLE

postgres=# INSERT INTO foo VALUES(generate_series(1,10,2), 'foo insert at ' || clock_timestamp());
INSERT 0 5

postgres=# SELECT * FROM foo ORDER BY id;
 id |                     val
----+---------------------------------------------
  1 | foo insert at 2022-10-23 11:12:21.758042+01
  3 | foo insert at 2022-10-23 11:12:21.758072+01
  5 | foo insert at 2022-10-23 11:12:21.758076+01
  7 | foo insert at 2022-10-23 11:12:21.758079+01
  9 | foo insert at 2022-10-23 11:12:21.758082+01
(5 rows)

postgres=# INSERT INTO bar VALUES(generate_series(1,10), 'bar insert at ' || clock_timestamp());
INSERT 0 10

postgres=# MERGE INTO foo f
             USING (SELECT id, val FROM bar) AS b
             ON f.id = b.id
           WHEN MATCHED THEN
             UPDATE SET val = b.val
           WHEN NOT MATCHED THEN
             INSERT (id, val)
             VALUES (b.id, b.val);
MERGE 10

postgres=# SELECT * FROM foo ORDER BY id;
 id |                     val
----+---------------------------------------------
  1 | bar insert at 2022-10-23 11:12:44.956601+01
  2 | bar insert at 2022-10-23 11:12:44.95663+01
  3 | bar insert at 2022-10-23 11:12:44.956633+01
  4 | bar insert at 2022-10-23 11:12:44.956636+01
  5 | bar insert at 2022-10-23 11:12:44.956639+01
  6 | bar insert at 2022-10-23 11:12:44.956641+01
  7 | bar insert at 2022-10-23 11:12:44.956643+01
  8 | bar insert at 2022-10-23 11:12:44.956645+01
  9 | bar insert at 2022-10-23 11:12:44.956647+01
 10 | bar insert at 2022-10-23 11:12:44.956649+01
(10 rows)

It is not possible to use a foreign table as the target table:

postgres=# MERGE INTO foreign_foo f
             USING (SELECT id, val FROM bar) AS b
             ON f.id = b.id
           WHEN MATCHED THEN
             UPDATE SET val = b.val
           WHEN NOT MATCHED THEN
             INSERT (id, val)
             VALUES (b.id, b.val);
ERROR:  cannot execute MERGE on relation "foreign_foo"
DETAIL:  This operation is not supported for foreign tables.
  • PostgreSQL documentation: MERGE

Categories

DML, SQL command