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 );1.2.3.4.5.

然后测试一下 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 | 11.2.3.4.5.6.7.8.9.10.11.12.

对于 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 | 21.2.3.4.5.6.7.8.9.10.11.

对于 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 | 21.2.3.4.5.6.7.8.9.10.11.

提示：对于 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 | UPDATE1.2.3.4.5.6.7.8.9.10.11.12.

源表提供了 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 | 51.2.3.4.5.6.7.8.9.10.11.12.13.14.

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

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

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