PostgreSQL 17新特性之MERGE语句增强

PostgreSQL 15 提供了 MERGE 语句，它可以基于源表或者查询结果更新目标表中的数据。MERGE 可以在单个语句中实现 INSERT、UPDATE 以及 DELETE 操作。

PostgreSQL 17 进一步增强了该语句的功能，包括：

• 支持 RETURNING 子句，可以返回新增、更新或者删除的数据行；
• 支持 WHEN NOT MATCHED BY SOURCE 操作，用于操作源表中不存在但是目标表中存在的数据行。

RETURNING 子句

PostgreSQL 支持 INSERT、UPDATE 以及 DELETE 语句的 RETURNING 子句，用于返回新增、更新或者删除的数据行。新版本则为 MERGE 语句提供了相同的功能。

我们先创建一个测试表：

CREATE TABLE test (
    id    INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tag   VARCHAR(10) NOT NULL UNIQUE,
    posts INT NOT NULL DEFAULT 0
);

然后测试一下 MERGE 语句新增数据时的操作：

MERGE INTO test t
USING (VALUES ('pg17')) AS s(tag)
ON t.tag = s.tag
WHEN MATCHED THEN
    UPDATE SET posts = posts + 1
WHEN NOT MATCHED THEN
    INSERT (tag, posts) VALUES (s.tag, 1)
RETURNING *;

  tag   | id | tag      | posts 
--------+----+----------+-------------
 pg17   |  1 | pg17     |           1

对于 INSERT 操作，RETURNING 返回了插入之后的数据。同时，RETURNING * 还返回了源表中的数据。

接下来是更新数据的操作：

MERGE INTO test t
USING (VALUES ('pg17')) AS s(tag)
ON t.tag = s.tag
WHEN MATCHED THEN
    UPDATE SET posts = posts + 1
WHEN NOT MATCHED THEN
    INSERT (tag, posts) VALUES (s.tag, 1)
RETURNING *;
  tag   | id | tag      | posts 
--------+----+----------+-------------
 pg17   |  1 | pg17     |           2

对于 UPDATE 操作，RETURNING 返回了更新之后的数据。

如果只需要返回目标表中更新后的数据，可以使用 RETURNING t.*：

MERGE INTO test t
USING (VALUES ('pg17')) AS s(tag)
ON t.tag = s.tag
WHEN MATCHED THEN
    UPDATE SET posts = posts + 1
WHEN NOT MATCHED THEN
    INSERT (tag, posts) VALUES (s.tag, 1)
RETURNING t.*;
 id | tag      | posts 
----+----------+-------------
  1 | pg17     |           2

提示：对于 DELETE 操作，RETURNING 返回删除之前的数据。

除此之外，新版本还增加了一个 merge_action() 函数，用于返回 MERGE 操作类型。函数的返回值包括 INSERT、UPDATE 以及 DELETE。例如：

MERGE INTO test t
USING (VALUES ('sql'),('pg17')) AS s(tag)
ON t.tag = s.tag
WHEN MATCHED THEN
    UPDATE SET posts = posts + 1
WHEN NOT MATCHED THEN
    INSERT (tag, posts) VALUES (s.tag, 1)
RETURNING t.*, merge_action();
 id | tag      | posts       | merge_action 
----+----------+-------------+--------------
  2 | sql      |           1 | INSERT
  1 | pg17     |           4 | UPDATE

源表提供了 2 条记录，其中一条在目标表中执行了 INSERT 操作，另一条执行了 UPDATE 操作。

merge_action() 函数只能用于 MERGE 语句的 RETURNING 字段列表。

WHEN NOT MATCHED BY SOURCE

PostgreSQL 17 还为 MERGE 语句增加了一个新的操作选项：WHEN NOT MATCHED BY SOURCE。该选项可以针对目标表中存在、但是源表中不存在的数据行进行操作，允许的操作包括 UPDATE、DELETE 或者 DO NOTHING。

例如：

MERGE INTO test t
USING (VALUES ('pg17')) AS s(tag)
ON t.tag = s.tag
WHEN MATCHED THEN
    UPDATE SET posts = posts + 1
WHEN NOT MATCHED THEN
    INSERT (tag, posts) VALUES (s.tag, 1)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SELECT * FROM test;
 id | username | touch_count 
----+----------+-------------
  1 | pg17     |           5

源表只提供了 1 条记录，目标表存在 2 条记录，我们删除了目标表中多出的一条数据（ tag = ‘sql’）。

默认的 WHEN NOT MATCHED 选项等价于 WHEN NOT MATCHED BY TARGET，用于针对源表中存在、但是目标表中不存在的数据行进行操作，允许的操作包括 INSERT 或者 DO NOTHING。

以前我们做数据 ETL 时，考虑到源表数据可能被删除，这时候目标表也需要做相同的操作，需要增加额外的 DELETE 语句，新版本 PostgreSQL 只需要一个 MERGE 语句解决增删改问题，完美！