PostgreSQL Upsert值相同时仍生成新版本行，如何阻止？

这是预期行为吗？ #

是的，这是PostgreSQL的预期行为。

当使用INSERT ... ON CONFLICT DO UPDATE时，只要触发了冲突（目标行已存在），不管DO UPDATE后的WHERE条件是否满足更新要求，PostgreSQL都会对该行获取排他锁，并且更新xmax字段（标记为当前事务ID）。哪怕最终没有修改任何列值，冲突处理流程已经启动，数据库会标记该行被当前事务处理过，因此xmax会递增。

而常规UPDATE语句不同：如果WHERE条件判断出无需修改（比如值完全相同），数据库不会触发任何行版本变更，xmax保持不变。

怎么阻止无变更时生成新行？ #

要避免xmax不必要的递增，需要让语句根本不进入冲突处理分支。可以通过CTE组合UPDATE和INSERT的逻辑，只在真正需要修改或插入时才操作：

WITH new_data AS (
    -- 替换为你要插入/更新的实际数据
    SELECT 1 AS id, 'hello' AS name
),
-- 先更新存在且值不同的行
updated_rows AS (
    UPDATE upsert_test
    SET name = new_data.name
    FROM new_data
    WHERE upsert_test.id = new_data.id
      AND upsert_test IS DISTINCT FROM new_data
    RETURNING upsert_test.id
)
-- 插入不存在的行
INSERT INTO upsert_test (id, name)
SELECT id, name FROM new_data
WHERE NOT EXISTS (
    SELECT 1 FROM updated_rows WHERE id = new_data.id
)
AND NOT EXISTS (
    SELECT 1 FROM upsert_test WHERE id = new_data.id
);

这个逻辑的核心是：

• 先尝试更新已存在但值不同的行，返回被更新的ID
• 只对既没被更新、也不存在的行执行插入操作

这样一来，当目标行已存在且值完全相同时，既不会触发更新，也不会触发插入，xmax不会发生变化。

补充说明 #

这种方式虽然比单条Upsert语句稍复杂，但能严格控制行版本的生成，适合你这种“无变更概率高”的场景。如果表字段较多，IS DISTINCT FROM会自动对比所有列，无需逐一指定，使用起来依然方便。

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