PostgreSQL UPSERT冲突更新时排除指定列的实现方法

你当前编写的语句无法触发冲突更新，核心问题有3个：

• 语句末尾配置的ON CONFLICT (id) DO NOTHING逻辑，本身的作用就是主键冲突时直接跳过操作、不做任何变更，自然不会执行更新
• 前置定义的del CTE会先删除ID匹配的旧记录再执行插入，不仅逻辑冗余，还会直接丢失原有记录的created_date、created_by原始值
• 定义dataCTE时仅给第一列指定了列名id，剩余列没有和表结构匹配的显式列定义，很容易出现字段顺序错位、数据写入错误的问题

WITH data(id, text, extra, vndr_code, item_code, created_date, created_by, modified_date, modified_by) AS (
   VALUES
    (5, 'foo5', 'sample_data_test5_view', 12345, '55555', now()::timestamp(0), 'user_name', now()::timestamp(0), 'user_name'),
    (6, 'foo6', 'sample_data_test6', 12345, '55555', now()::timestamp(0), 'user_name', now()::timestamp(0), 'user_name')
)
INSERT INTO tbl AS t
SELECT * FROM data
ON CONFLICT (id) DO UPDATE SET
    text = EXCLUDED.text,
    extra = EXCLUDED.extra,
    vndr_code = EXCLUDED.vndr_code,
    item_code = EXCLUDED.item_code,
    modified_date = EXCLUDED.modified_date,
    modified_by = EXCLUDED.modified_by
-- 可选：追加以下条件避免新值与旧值完全一致时执行空更新
-- WHERE t IS DISTINCT FROM EXCLUDED
RETURNING t.id;

• EXCLUDED是PostgreSQL原生UPSERT语法的特殊内置对象，代表触发主键冲突时，原本尝试插入的新记录的字段值
• 上述更新逻辑没有覆盖id、created_date、created_by三个字段：主键本身不需要更新，创建时间、创建人会在冲突时自动保留表中原有的值，完全匹配需求
• 移除了原语句中冗余的前置DELETE逻辑，减少无意义的IO操作，也避免了先删后插导致的原始创建信息丢失问题
• 给dataCTE补全了和目标表完全对应的列名定义，保证字段映射关系正确，不会出现错位写入的问题

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