PostgreSQL UPDATE语句如何实现类似ON CONFLICT DO NOTHING跳过约束冲突

核心原因 #

PostgreSQL 仅INSERT语句支持ON CONFLICT语法，原生UPDATE语句不支持该特性，因此你之前的写法无法生效。

推荐解决方案 #

直接在UPDATE的WHERE条件中添加冲突校验逻辑，提前过滤掉会触发唯一约束冲突的记录，不会修改已有的正常数据，也不会触发约束报错。

执行更新的SQL语句 #

UPDATE moa.part p
SET po_number = TRIM(LEADING '0' FROM p.po_number)
WHERE 
  -- 仅处理带前导零的po_number
  p.po_number LIKE '0%'
  -- 排除trim后会和已有记录冲突的条目
  AND NOT EXISTS (
    SELECT 1 
    FROM moa.part p2
    WHERE 
      p2.part_number = p.part_number
      AND p2.esn_id = p.esn_id
      AND p2.tenant_id = p.tenant_id
      AND p2.po_number = TRIM(LEADING '0' FROM p.po_number)
  );

预校验语句（执行更新前建议先运行确认更新范围） #

SELECT 
  p.part_id,
  p.part_number,
  p.esn_id,
  p.tenant_id,
  p.po_number AS old_po_number,
  TRIM(LEADING '0' FROM p.po_number) AS new_po_number
FROM moa.part p
WHERE 
  p.po_number LIKE '0%'
  AND NOT EXISTS (
    SELECT 1 
    FROM moa.part p2
    WHERE 
      p2.part_number = p.part_number
      AND p2.esn_id = p.esn_id
      AND p2.tenant_id = p.tenant_id
      AND p2.po_number = TRIM(LEADING '0' FROM p.po_number)
  );

方案说明 #

• 该方案复用你已有的联合唯一索引part_4col_uni_idx做校验，执行效率高，不会出现全表扫描的性能问题
• 所有会触发冲突的记录都会被直接跳过，不会改动已存在的无前置零的正常数据

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