Postgres 无需触发器实现新旧值相等时跳过更新的单查询方案

可以在单条SQL语句中实现所有需求，无需自定义函数或触发器，PostgreSQL原生的INSERT ... ON CONFLICT语法完全支持该逻辑。

JSONB类型原生支持直接等值/不等值比较，无需转为文本判断，假设你的示例表名为app_data，完整实现语句如下：

INSERT INTO app_data (id, name, metadata, version)
VALUES
    -- 此处替换为你实际的混合数据集
    (1, 'user1', '{"role": "admin", "level": 3}'::jsonb, DEFAULT),
    (2, 'user2', '{"role": "editor", "level": 2}'::jsonb, DEFAULT),
    (3, 'user3', '{"role": "viewer", "level": 1}'::jsonb, DEFAULT)
ON CONFLICT (name) DO UPDATE
SET
    metadata = EXCLUDED.metadata,
    version = app_data.version + 1
-- 仅元数据变更时才执行更新
WHERE app_data.metadata <> EXCLUDED.metadata;

逻辑匹配说明 #

• 新增场景：传入的name在表中不存在时，不会触发冲突逻辑，直接执行插入，version使用默认值0，符合要求。
• 已变更场景：传入的name已存在，且原有行metadata和新传入的EXCLUDED.metadata不相等时，触发更新，覆盖metadata字段并将version加1，符合要求。
• 未变更场景：传入的name已存在，且原有行metadata和新传入值相等时，不满足WHERE过滤条件，不会执行更新，version保持不变，符合要求。

提示：如果业务允许metadata字段为NULL，建议将WHERE条件替换为app_data.metadata IS DISTINCT FROM EXCLUDED.metadata，可以兼容NULL值的等值判断场景。
你之前认为ON CONFLICT分支无法获取原有行数据属于误解，在DO UPDATE子句中可以直接通过表名引用原有行的所有字段值，和代表新插入值的EXCLUDED伪表字段对比即可实现条件更新。

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