目录

MERGE

用于将一个关系（表）中的行合并到另一个关系中的 SQL 命令

MERGE 是一个 DML 命令，用于有条件地将一个关系中的行合并到另一个关系中。

MERGE 在 PostgreSQL 15 中被添加。

变更历史

示例

关于 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)

不能使用 外表 作为目标表

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 文档： MERGE

分类

DML, SQL 命令

反馈

在此处提交有关“MERGE”的任何评论、建议或更正 此处。