PostgreSQL执行ON CONFLICT DO UPDATE无法二次更新行报错求助

报错原因 #

这个错误的核心触发条件是：同一个INSERT命令返回的待插入数据中，存在多组重复的(prompt_input_value, collect_project_id)唯一约束组合。PostgreSQL的ON CONFLICT DO UPDATE不允许同一批插入数据里出现重复的约束键，因为无法判断应该用哪条重复数据执行更新操作。
你当前场景下具体的诱因有两个：

• 你提供的示例source数据中，两个不同的输入项（环境类型ambient、噪音类型Noise type）的可选值数组里都包含空白字符串，同一个projectid下就会出现两条(20030, "")的重复约束组合
• 原SQL的SELECT返回字段顺序和INSERT指定的字段顺序不匹配，prompt_input_desc和prompt_input_name的插入顺序错位，也可能间接触发未知的重复问题

修复后的SQL #

INSERT INTO target.dim_collect_user_inp_configs AS t (
    collect_project_id,
    prompt_type,
    prompt_input_desc,
    prompt_input_name,
    prompt_input_value,
    script_id,
    corpuscode
)
SELECT DISTINCT ON (s.projectid, prompt_input_value)
    s.projectid,
    s.prompttype,
    el.inputs->>'desc' AS prompt_input_desc,
    el.inputs->>'name' AS prompt_input_name,
    (jsonb_array_elements(el.inputs->'values')) #>> '{}' AS prompt_input_value,
    s.scriptid,
    s.corpuscode
FROM source.staticprompts AS s,
     jsonb_array_elements(s.inputs::jsonb) el(inputs)
-- 重复约束组合下优先取源表中最新修改的记录
ORDER BY s.projectid, prompt_input_value, s.modified DESC
ON CONFLICT (prompt_input_value, collect_project_id)
DO UPDATE SET
    prompt_input_desc = EXCLUDED.prompt_input_desc,
    prompt_input_name = EXCLUDED.prompt_input_name,
    date_updated = NOW()
WHERE t.prompt_input_desc != EXCLUDED.prompt_input_desc
   OR t.prompt_input_name != EXCLUDED.prompt_input_name
RETURNING *;

关键调整说明 #

• 新增DISTINCT ON (s.projectid, prompt_input_value)逻辑，对待插入数据按唯一约束字段去重，保证每个组合只有一行数据
• 修正字段顺序错位问题：将SELECT返回的desc和name字段顺序调整为和INSERT指定的顺序完全一致，避免字段值插错
• 把JSON数组拆出的枚举值用#>> '{}'转成纯字符串，和目标表prompt_input_value的varchar类型完全匹配，避免类型隐式转换导致的约束匹配异常
• 新增ORDER BY规则，重复的约束组合下优先取source表中modified时间最新的记录，保证同步的数据是最新版本

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