PostgreSQL使用INSERT FROM SELECT搭配ON CONFLICT插入报唯一约束错误

错误原因 #

报错核心是目标表target_redshift.user_input_conf的表结构定义存在不符合业务逻辑的约束：

• 建表语句中给collect_project_id字段单独设置了UNIQUE约束，该约束要求整张表中每个collect_project_id只能存在1条记录
• 但你的迁移逻辑是允许同一个采集项目（collect_project_id）下存在多条不同prompt_input_value的记录，仅要求二者的组合唯一。当插入第二条相同collect_project_id、不同prompt_input_value的记录时，就会触发单独的唯一约束校验失败，抛出该报错。
• 额外注意：表中给prompt_input_value也单独设置了UNIQUE约束，同样不符合业务规则，会导致不同采集项目无法使用相同的prompt_input_value，后续也会触发同类报错。

解决办法 #

步骤1：删除多余的单独唯一约束 #

执行以下SQL删除不符合业务逻辑的两个单独唯一约束，仅保留业务要求的联合唯一约束must_be_different：

-- 删除collect_project_id的单独唯一约束
ALTER TABLE target_redshift.user_input_conf 
DROP CONSTRAINT user_input_conf_collect_project_id_key;

-- 可选：如果业务允许不同采集项目使用相同的prompt_input_value，删除该字段的单独唯一约束
ALTER TABLE target_redshift.user_input_conf 
DROP CONSTRAINT user_input_conf_prompt_input_value_key;

步骤2：校验源表重复数据 #

执行以下查询确认源表中提取的(collect_project_id, prompt_input_value)组合没有重复，避免触发合法的联合唯一约束报错：

SELECT 
  s.projectid AS collect_project_id,
  s.inputs::jsonb#>>'{inputs,0,values}' AS prompt_input_value,
  COUNT(*) AS 重复条数
FROM source_redshift.staticprompts AS s
GROUP BY 1,2
HAVING COUNT(*) > 1;

如果查询返回结果，先对源表重复数据做去重处理后再执行迁移。

步骤3：重新执行原迁移SQL #

约束调整和源数据校验完成后，你原本编写的INSERT ON CONFLICT迁移语句即可正常运行。

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