Postgres实现含NOT MATCHED BY子句的MERGE功能的简洁方案

PostgreSQL 15开始支持MERGE语句，但语法和部分数据库（如SQL Server、Oracle）存在差异，不支持WHEN NOT MATCHED BY TARGET和WHEN NOT MATCHED BY SOURCE写法。其中WHEN NOT MATCHED默认对应目标表无匹配的场景，而源表无匹配时删除目标记录的需求需要额外处理。

方案：事务内结合MERGE与DELETE（原子性操作） #

将更新、插入、删除操作封装在同一个事务中，确保数据一致性：

BEGIN;

-- 处理匹配更新、目标无匹配插入
MERGE INTO schema1.target_table AS t
USING schema2.source_table AS s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET
        data1 = s.data1,
        data2 = s.data2
WHEN NOT MATCHED THEN
    INSERT (id, data1, data2)
    VALUES (s.id, s.data1, s.data2);

-- 处理源无匹配的删除：删除目标表中不在源表的记录
DELETE FROM schema1.target_table t
WHERE NOT EXISTS (
    SELECT 1 FROM schema2.source_table s
    WHERE s.id = t.id
);

COMMIT;

关键细节 #

• 原代码中INSERT (id, data1, data2,)的列列表末尾多了逗号，属于语法错误，必须删除。
• PostgreSQL的WHEN NOT MATCHED等价于其他数据库的WHEN NOT MATCHED BY TARGET，无需额外指定BY TARGET关键字。
• 源表无匹配时删除目标记录的逻辑无法通过MERGE直接实现，需单独使用DELETE语句结合NOT EXISTS子句完成；事务包裹可保证所有操作要么全部成功，要么全部回滚，避免数据不一致。

内容的提问来源于stack exchange，提问作者leadvic