PostgreSQL 17新特性之MERGE語(yǔ)句增強(qiáng)
PostgreSQL 15 提供了 MERGE 語(yǔ)句，它可以基于源表或者查詢結(jié)果更新目標(biāo)表中的數(shù)據(jù)。MERGE 可以在單個(gè)語(yǔ)句中實(shí)現(xiàn) INSERT、UPDATE 以及 DELETE 操作。
PostgreSQL 17 進(jìn)一步增強(qiáng)了該語(yǔ)句的功能，包括：
- 支持 RETURNING 子句，可以返回新增、更新或者刪除的數(shù)據(jù)行；
- 支持 WHEN NOT MATCHED BY SOURCE 操作，用于操作源表中不存在但是目標(biāo)表中存在的數(shù)據(jù)行。
RETURNING 子句
PostgreSQL 支持 INSERT、UPDATE 以及 DELETE 語(yǔ)句的 RETURNING 子句，用于返回新增、更新或者刪除的數(shù)據(jù)行。新版本則為 MERGE 語(yǔ)句提供了相同的功能。
我們先創(chuàng)建一個(gè)測(cè)試表：
CREATE TABLE test (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tag VARCHAR(10) NOT NULL UNIQUE,
posts INT NOT NULL DEFAULT 0
);然后測(cè)試一下 MERGE 語(yǔ)句新增數(shù)據(jù)時(shí)的操作：
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對(duì)于 INSERT 操作，RETURNING 返回了插入之后的數(shù)據(jù)。同時(shí)，RETURNING * 還返回了源表中的數(shù)據(jù)。
接下來(lái)是更新數(shù)據(jù)的操作：
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對(duì)于 UPDATE 操作，RETURNING 返回了更新之后的數(shù)據(jù)。
如果只需要返回目標(biāo)表中更新后的數(shù)據(jù)，可以使用 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提示：對(duì)于 DELETE 操作，RETURNING 返回刪除之前的數(shù)據(jù)。
除此之外，新版本還增加了一個(gè) merge_action() 函數(shù)，用于返回 MERGE 操作類型。函數(shù)的返回值包括 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 條記錄，其中一條在目標(biāo)表中執(zhí)行了 INSERT 操作，另一條執(zhí)行了 UPDATE 操作。
merge_action() 函數(shù)只能用于 MERGE 語(yǔ)句的 RETURNING 字段列表。
WHEN NOT MATCHED BY SOURCE
PostgreSQL 17 還為 MERGE 語(yǔ)句增加了一個(gè)新的操作選項(xiàng)：WHEN NOT MATCHED BY SOURCE。該選項(xiàng)可以針對(duì)目標(biāo)表中存在、但是源表中不存在的數(shù)據(jù)行進(jìn)行操作，允許的操作包括 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 條記錄，目標(biāo)表存在 2 條記錄，我們刪除了目標(biāo)表中多出的一條數(shù)據(jù)（ tag = ‘sql’）。
默認(rèn)的 WHEN NOT MATCHED 選項(xiàng)等價(jià)于 WHEN NOT MATCHED BY TARGET，用于針對(duì)源表中存在、但是目標(biāo)表中不存在的數(shù)據(jù)行進(jìn)行操作，允許的操作包括 INSERT 或者 DO NOTHING。
以前我們做數(shù)據(jù) ETL 時(shí)，考慮到源表數(shù)據(jù)可能被刪除，這時(shí)候目標(biāo)表也需要做相同的操作，需要增加額外的 DELETE 語(yǔ)句，新版本 PostgreSQL 只需要一個(gè) MERGE 語(yǔ)句解決增刪改問(wèn)題，完美！
