Postgres 导入数据报错 ERROR duplicate key value 如何处理

Postgres 导入数据出现 ERROR duplicate key value 通常是因为插入的数据违反了主键或唯一约束。最推荐的处理方式是先在测试环境确认冲突数据量，再决定使用 ON CONFLICT 忽略冲突或清洗源数据。

先说结论：该报错属于唯一约束冲突，生产环境严禁直接关闭约束，应优先通过 SQL 语法处理冲突或清洗源数据。

先确认：查看报错信息中的 constraint_name 确定是哪张表的哪个字段冲突
先处理：根据业务需求选择 ON CONFLICT 跳过重复行或更新现有行
再验证：导入完成后对比源数据与目标表的行数差异

命令速用版

-- 忽略冲突行继续导入
INSERT INTO table_name (id, col) VALUES (1, 'data')
ON CONFLICT (id) DO NOTHING;

-- 冲突时更新现有行
INSERT INTO table_name (id, col) VALUES (1, 'data')
ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col;

-- 修复自增序列（如果手动导入了 ID）
SELECT setval('table_name_id_seq', (SELECT MAX(id) FROM table_name));

为什么会这样

Postgres 强制保证主键和唯一约束列的数据不重复。导入数据时，如果源数据包含已存在的键值，数据库会抛出错误代码 23505 (unique_violation) 并终止当前语句。

分步处理

步骤 1：定位约束名称
适用场景：报错信息不明确时。
操作动作：查看终端报错日志，找到 violates unique constraint "xxx" 部分。
风险边界：不要忽略约束名称，不同约束代表不同业务含义。

步骤 2：选择处理策略
适用场景：确认数据冲突原因后。
操作动作：若允许丢弃重复数据，使用 ON CONFLICT DO NOTHING；若需更新最新数据，使用 DO UPDATE。
风险边界：DO UPDATE 会覆盖现有数据，需确认业务逻辑允许覆盖。

步骤 3：执行导入或修复
适用场景：正式执行数据迁移。
操作动作：在事务块中执行导入语句，失败可回滚。
风险边界：大数据量导入建议分批提交，避免长事务锁表。

Postgres 导入数据报错 ERROR duplicate key value 如何处理

怎么验证是否生效

执行 SELECT count(*) FROM table_name 检查目标表行数是否符合预期。查看数据库日志文件（通常在 data/log 目录下），确认没有新的 23505 错误记录。对比源文件行数与导入成功行数，差异值应等于预期的冲突跳过数。

常见坑

自增序列不同步：手动导入 ID 后未更新 sequence，导致后续自动插入再次报错。
外键约束干扰：解决了主键冲突但触发了外键约束错误，需按顺序导入父表。
大小写敏感：约束名称在报错中可能带引号，区分大小写。

常见问题

报错信息里的 key value 具体指什么

指实际冲突的数据值。报错日志通常包含 Key=(id)=(1) 这样的片段，括号内的数字或字符串就是导致冲突的具体内容。

COPY 命令导入时如何处理重复数据

标准 COPY 命令不支持 ON CONFLICT。建议先将数据 COPY 到临时表，再通过 INSERT INTO ... SELECT ... ON CONFLICT 从临时表写入正式表。

如何重置自增序列避免后续报错

使用 setval 函数将序列当前值设置为表内最大 ID。命令为 SELECT setval('序列名', (SELECT MAX(id) FROM 表名));。

参考来源

PostgreSQL Official Documentation - INSERT (SQL Command), URL: https://www.postgresql.org/docs/current/sql-insert.html
PostgreSQL Official Documentation - Appendix A. PostgreSQL Error Codes, URL: https://www.postgresql.org/docs/current/errcodes-appendix.html