PostgreSQL 17: 支持 MERGE ... RETURNING

由 John Doe 五月 21, 2025

你希望批量加载数据进表时，返回合并的差异行吗？现在，PostgreSQL 已经支持 MERGE … RETURNING 了。

在山坡漫步的大象

特性提交日志

支持 MERGE … RETURNING 语法。

这允许在 MERGE 查询的后面附加 RETURNING 子句，以返回基于每行插入、更新或删除的值。与普通的 INSERT、UPDATE 和 DELETE 命令一样，对于 INSERT 和 UPDATE 操作返回的值是目标表上的新内容，对于 DELETE 操作则是目标表上的旧内容。源表中的值也可以被返回。

与 INSERT/UPDATE/DELETE 一样，MERGE ... RETURNING的输出可以用作其他操作的源表，例如 WITH 查询和 COPY 命令。

此外，还提供了一个特殊函数merge_action()，它根据为每行执行的操作返回’INSERT’、‘UPDATE’或’DELETE’。merge_action() 函数可以在 RETURNING 列表中的任何位置使用，包括在任意表达式和子查询中，但在 MERGE 查询的 RETURNING 列表之外使用该函数会出错。

讨论：http://postgr.es/m/CAEZATCWePEGQR5LBn-vD6SfeLZafzEm2Qy_L_Oky2=qw2w3Pzg@mail.gmail.com

示例

我们在 PostgreSQL 15 中引入了 MERGE，尽管它很强大，却缺少像 INSERT、UPDATE 或 DELETE 那样使用标准 RETURNING 子句返回插入/更新数据的功能。现在，我们有这个功能了。

那么，让我们看看它是如何工作的。首先，创建一个测试表：

CREATE TABLE test (
    id int8 generated always AS IDENTITY,
    username text NOT NULL UNIQUE,
    touch_count int4 NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);

下面来看执行插入操作时使用 RETURNING 的 MERGE 语句：

MERGE INTO test t
USING (VALUES ('redrock')) AS i(un)
ON t.username = i.un
WHEN matched THEN
    UPDATE SET touch_count = touch_count + 1
WHEN NOT matched THEN
    INSERT (username, touch_count) VALUES (i.un, 1)
RETURNING *;
    un   | id | username | touch_count
---------+----+----------+-------------
 redrock |  1 | redrock  |           1
(1 row)

很好。有趣的是，RETURNING *会添加 un 列，这应该是产生匹配的值。

再来看执行更新操作的 MERGE 语句：

MERGE INTO test t
USING (VALUES ('redrock')) AS i(un)
ON t.username = i.un
WHEN matched THEN
    UPDATE SET touch_count = touch_count + 1
WHEN NOT matched THEN
    INSERT (username, touch_count) VALUES (i.un, 1)
RETURNING *;
    un   | id | username | touch_count
---------+----+----------+-------------
 redrock |  1 | redrock  |           2
(1 row)

此外，我们可以使用RETURNING t.*来仅获取表中的数据：

MERGE INTO test t
USING (VALUES ('redrock')) AS i(un)
ON t.username = i.un
WHEN matched THEN
    UPDATE SET touch_count = touch_count + 1
WHEN NOT matched THEN
    INSERT (username, touch_count) VALUES (i.un, 1)
RETURNING t.*;
 id | username | touch_count
----+----------+-------------
  1 | redrock  |           3
(1 row)

有一个辅助函数merge_action()，它会返回该行是被插入还是更新的信息：

MERGE INTO test t
USING (VALUES ('friend')) AS i(un)
ON t.username = i.un
WHEN matched THEN
    UPDATE SET touch_count = touch_count + 1
WHEN NOT matched THEN
    INSERT (username, touch_count) VALUES (i.un, 1)
RETURNING t.*, merge_action();
 id | username | touch_count | merge_action
----+----------+-------------+--------------
  2 | friend   |           1 | INSERT
(1 row)

再尝试进行更新操作：

MERGE INTO test t
USING (VALUES ('friend')) AS i(un)
ON t.username = i.un
WHEN matched THEN
    UPDATE SET touch_count = touch_count + 1
WHEN NOT matched THEN
    INSERT (username, touch_count) VALUES (i.un, 1)
RETURNING t.*, merge_action();
 id | username | touch_count | merge_action
----+----------+-------------+--------------
  2 | friend   |           2 | UPDATE
(1 row)