内容

合并

将一个关系中的行合并到另一个关系中的 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" 的评论、建议或更正 此处。