Postgres Upsert遇Cardinality Violation错误，冲突时设字段默认值

错误原因 #

你遇到的CardinalityViolation错误，核心原因是一次性插入的数据集里存在重复的job_id（主键）。Postgres在执行ON CONFLICT DO UPDATE时，同一条命令里多个重复的job_id会多次尝试更新目标表的同一行，导致系统无法确定使用哪一行数据来执行更新，从而抛出错误。

解决方案 #

要实现"冲突时将complete设为默认值"的需求，需先确保插入的数据集里每个job_id唯一，再执行冲突更新逻辑。以下是两种常用实现方式：

1. 对插入数据源提前去重（推荐） #

如果你的数据来自其他表或VALUES列表，先通过去重操作保证每个job_id只出现一次：

场景1：VALUES列表存在重复

将VALUES放在子查询中，用DISTINCT去重：

INSERT INTO site_notes (job_id, attachment_id)
SELECT DISTINCT job_id, attachment_id
FROM (
    VALUES 
        ('job001', 'att001'),
        ('job002', 'att002'),
        ('job001', 'att001') -- 重复的job_id
) AS temp_data(job_id, attachment_id)
ON CONFLICT (job_id) DO UPDATE
SET complete = DEFAULT;

场景2：从其他表提取数据

直接在SELECT语句中用GROUP BY或DISTINCT去重：

INSERT INTO site_notes (job_id, attachment_id)
SELECT job_id, attachment_id
FROM your_source_table
GROUP BY job_id, attachment_id -- 确保每个job_id唯一
ON CONFLICT (job_id) DO UPDATE
SET complete = DEFAULT;

2. 保留特定版本的重复数据 #

如果重复的job_id对应不同的attachment_id，需要保留特定版本（比如最新的），可以用窗口函数筛选：

INSERT INTO site_notes (job_id, attachment_id)
SELECT job_id, attachment_id
FROM (
    SELECT 
        job_id, 
        attachment_id,
        -- 按时间戳排序，保留最新的一条数据
        ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY create_time DESC) AS row_num
    FROM your_source_table
) AS filtered_data
WHERE row_num = 1
ON CONFLICT (job_id) DO UPDATE
SET complete = DEFAULT;

关键说明 #

• SET complete = DEFAULT可以直接使用表定义的默认值（这里是0），无需手动写SET complete = 0，保持和表结构的一致性。
• 必须优先处理插入数据中的重复主键，这是解决该错误的核心前提。

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