PostgreSQL 15 特性 - MERGE INTO 语法

MERGE INTO 语法很早之前就在 Oracle 数据库中实现了,最近 PostgreSQL 也实现了相应的语法,该语法符合 SQL2016 标准,与 Oracle 的 MERGE INTO 还是有一定区别的。本文简要介绍一下 PostgreSQL 中的 MERGE INTO 语法。

语法

PostgreSQL 提供的 MERGER INTO 语法如下所示。

1
2
3
4
[ WITH with_query [, ...] ]
MERGE INTO target_table_name [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]

data_source 可以是表、视图或子查询,如下所示。

1
{ source_table_name | ( source_query ) } [ [ AS ] source_alias ]

when_clause 子句用于给出具体的执行动作。当匹配上时,可以执行 UPDATEDELETE 操作;当未匹配上时,则可以执行 INSERT 操作。

1
2
{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }

merge_insertmerge_updatemerge_delete 的语法如下所示:

1
2
3
4
5
6
7
8
INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

DELETE

对比 Oracle 的 MERGE INTO 语法,还是可以发现它们存在很大的差异。

Oracle 中的 merge_insert_clausemerge_delete_clausemerge_update_clause 中是可以携带 WHERE 子句的,然而在 PostgreSQL 中则是将其放置在 when_clause 子句中了。

PostgreSQL 中的 MERGE INTO 执行步骤如下所示:

  1. 无论 WHEN 子句匹配与否,为所有的操作执行 BEFORE STATEMENT 触发器。
  2. 执行从源表到目标表的连接。生成的查询将正常优化并生成一组候选的更改行。对于每个候选更改行,执行下面的步骤:
    a. 评估每一行是 MATCHED 还是 NOT MATCHED
    b. 按指定的顺序测试每个 WHEN 条件,直到返回 true
    c. 当条件返回 true 时,执行以下操作:
    • 执行操作的事件类型触发的 BEFORE ROW 触发器。
    • 执行指定的操作,对目标表调用任何检查约束。
    • 执行操作的事件类型触发的 AFTER ROW 触发器。
  3. 为指定的操作执行 AFTER STATEMENT 触发器,无论它们是否实际发生。这类似于不修改任何行的 UPDATE 语句的行为。

示例

首先,我们创建 targetsource 表,并向 target 表中插入三条记录,向 source 表插入一条记录,如下所示。

1
2
3
4
5
6
CREATE TABLE target (tid integer, balance integer);
CREATE TABLE source (sid integer, delta integer);
INSERT INTO target VALUES (1, 10);
INSERT INTO target VALUES (2, 20);
INSERT INTO target VALUES (3, 30);
INSERT INTO source VALUES (4, 40);

我们先来测试一下 MATCHED ... UPDATE 功能。

1
2
3
4
5
MERGE INTO target t
USING source AS s
ON t.tid = s.sid
WHEN MATCHED THEN
UPDATE SET balance = 0;
1
MERGE 0

可以看到没有任何记录发生变化,符合预期。

接下来,我们测试一下 NOT MATCHED ... INSERT 功能。

1
2
3
4
5
MERGE INTO target t
USING source AS s
ON t.tid = s.sid
WHEN NOT MATCHED THEN
INSERT VALUES (4, 45);
1
MERGE 1

查看 target 表,如下所示。

1
SELECT * FROM target;
1
2
3
4
5
6
7
 tid | balance
-----+---------
1 | 10
2 | 20
3 | 30
4 | 45
(4 rows)

这说明 tid = 4 的记录未匹配上,并正确插入到了 target 表中。

最后,我们来综合测试一下。

1
2
3
4
5
6
7
8
9
10
INSERT INTO source VALUES (5, 50), (2, 20);
MERGE INTO target t
USING source AS s
ON t.tid = s.sid
WHEN MATCHED AND s.sid < 3 THEN
UPDATE SET balance = t.balance * 10
WHEN MATCHED THEN
DELETE
WHEN NOT MATCHED THEN
INSERT VALUES (s.sid, s.delta);
1
MERGE 3

可以看到有三条记录发生了变化。我们期望的是当匹配的时候,如果 s.sid < 3 时,那么就将 target 表中的 balance 扩大 10 倍,否则就删除它;当未匹配的时候就插入 source 表中的记录到 target 表中。

source 表的内容如下所示。

1
2
3
4
5
6
 sid | delta
-----+-------
4 | 40
5 | 50
2 | 20
(3 rows)

因此,执行上述的 MERGE INTO 语句之后,target 表中 tid = 4 的记录应该被删除,tid = 2 的记录 balance 扩大 10 倍,而未匹配的 5, 50 则会插入到 target 表中。

1
SELECT * FROM target;
1
2
3
4
5
6
7
 tid | balance
-----+---------
1 | 10
3 | 30
2 | 200
5 | 50
(4 rows)

结果与预期一致。

需要注意的是,PostgreSQL 中的 MERGE INTO 语法没有提供特殊的权限管理,它依赖于底层 INSERTUPDATEDELETE 操作的权限控制。

参考

[1] https://www.postgresql.org/docs/devel/sql-merge.html
[2] https://docs.oracle.com/database/121/SQLRF/statements_9017.htm

笑林广记 - 斋戒库

一监生姓齐,家资甚富,但不识字。
一日府尊出票,取鸡二只,兔一只。
皂亦不识字,央齐监生看。
生曰:“讨鸡二只,免一只。”
皂只买一鸡回话。太守怒曰:“票上取鸡二只,兔一只,为何只缴一鸡?”
皂以监生事禀。太守遂拘监生来问,时太守适有公干,暂将监生收入斋戒库内候究。
生入库,见碑上斋戒二字,认做他父亲齐成姓名,张目惊诧呜咽不止。
人问何故,答曰:“先人灵座,何人设建在此,睹物伤情,焉得不哭。”