MERGE 是一个用于有条件地将一个关系中的行合并到另一个关系中的DML命令。
MERGE 在PostgreSQL 15中添加。
更改历史
- PostgreSQL 17
- PostgreSQL 15
- 已添加（提交7103ebb7）
示例
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
有用链接
- PostgreSQL 15：在SQL中使用MERGE - 2022年5月Hans-Jürgen Schönig / CyberTec发表的博文