PostgreSQL更新表遇唯一约束冲突时如何跳过对应行？

你遇到的问题是PostgreSQL的UPDATE语句不支持ON CONFLICT子句——这个语法仅适用于INSERT操作，用来处理插入时的唯一冲突，所以直接添加会报语法错误。

下面提供两种可行的解决方法：

方法一：纯SQL预检查冲突（高效推荐） #

通过子查询提前排除设置my_timestamp = null后会触发唯一约束的行。需要根据你的唯一约束my_constraint的具体列调整查询逻辑，假设约束是基于col_a和my_timestamp的组合唯一，示例如下：

UPDATE my_table
SET my_timestamp = null
WHERE TO_CHAR(my_timestamp :: DATE, 'dd-mm-yyyy') = '01-01-1970' 
  AND cast(my_timestamp as time) > '10:10:10'
  -- 排除当前行设为null后会与已有行冲突的情况
  AND NOT EXISTS (
    SELECT 1 
    FROM my_table t2
    -- 匹配唯一约束的所有列
    WHERE t2.col_a = my_table.col_a 
      AND t2.my_timestamp IS NULL
      -- 排除当前行自身，避免和自己对比
      AND t2.id != my_table.id
  );

如果你的唯一约束是单列（比如仅my_timestamp），逻辑会更简单，只需确保没有其他行的my_timestamp已经为null即可。

方法二：PL/pgSQL循环处理（无需了解约束细节） #

如果不确定唯一约束的具体列，或者场景更复杂，可以用PL/pgSQL写一个匿名块，逐行尝试更新，捕获唯一约束冲突错误并跳过：

DO $$
DECLARE
    rec RECORD;
BEGIN
    -- 先筛选出需要更新的行
    FOR rec IN SELECT id FROM my_table
               WHERE TO_CHAR(my_timestamp :: DATE, 'dd-mm-yyyy') = '01-01-1970' 
                 AND cast(my_timestamp as time) > '10:10:10'
    LOOP
        BEGIN
            -- 尝试更新单一行
            UPDATE my_table
            SET my_timestamp = null
            WHERE id = rec.id;
        EXCEPTION
            -- 捕获唯一约束冲突错误，跳过当前行
            WHEN unique_violation THEN
                CONTINUE;
        END;
    END LOOP;
END $$;

这种方法的缺点是逐行处理效率较低，适合数据量不大的场景。

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