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