// PostgreSQL 错误参考

PostgreSQL 23505:重复键违反唯一约束

INSERT 或 UPDATE 会在受主键、唯一约束或唯一索引保护的列中再产生一行相同的值。

SQLSTATE
23505
名称
unique_violation
类别
违反完整性约束
错误信息
duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

它的意思

PostgreSQL 强制执行每个主键, UNIQUE 约束和唯一索引会在行写入的那一刻进行检查。如果新行或修改后的行与已有行(或另一事务已写入但尚未提交的行)键值相同,该语句会以 23505 失败,它所做的更改一个都不会保留。

这 DETAIL 行会指出列名和重复的值,消息会指出约束或索引的名字,这样你就确切知道违反了哪条规则。

常见原因

  • 插入一个键已存在的行,例如重新运行导入或种子脚本。
  • A serial 或已经落后于数据的标识序列,通常是在使用显式 ID 加载行之后。下一个默认 ID 与现有行冲突:经典情况 Key (id)=(1) already exists.
  • 两个会话同时插入相同的键;第二个会话等待第一个完成,并在第一个提交后立即失败。
  • 一个 UPDATE 会把唯一列设置成另一行已有的值。

如何修复它

  1. 找到 DETAIL 行,并判断新数据是重复记录还是另一条不同的记录。

    SELECT * FROM users WHERE email = '[email protected]';
  2. 如果这次写入应当改为更新现有行,请把它写成 upsert,使用 ON CONFLICT.

    INSERT INTO users (email, name)
    VALUES ('[email protected]', 'Ada')
    ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
  3. 如果应直接跳过重复项,请使用 ON CONFLICT DO NOTHING.

    INSERT INTO users (email, name)
    VALUES ('[email protected]', 'Ada')
    ON CONFLICT (email) DO NOTHING;
  4. 如果重复出现在 serial 或标识 id 的情况,把序列推到现有最大值之后。

    SELECT setval(pg_get_serial_sequence('users', 'id'),
                  (SELECT COALESCE(MAX(id), 0) + 1 FROM users), false);

安全地复现它

CREATE TEMP TABLE t (id int PRIMARY KEY);
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (1);  -- ERROR: duplicate key value violates unique constraint "t_pkey"

问题

为什么我从未设置 id,插入却报 id 列重复键错误?

表的序列落后于数据,通常是因为插入行时用了显式 ID,或还原时没同步序列值。用 setval 将其重置到 MAX(id).

ON CONFLICT 是否适用于任何唯一索引?

它适用于唯一索引或约束的列,或者适用于 ON CONFLICT ON CONSTRAINT name。它需要 PostgreSQL 9.5 或更高版本。

相关错误

更多完整性约束冲突错误

代码和消息文本来自 PostgreSQL 文档,附录 A:PostgreSQL 错误代码。浏览完整的 PostgreSQL 错误列表 或者 搜索每个数据库错误.

// 错误更少,修复更快

在 SQLly 中使用 PostgreSQL

适用于 macOS、Windows 和 Linux 的原生 SQL 客户端,智能感知能理解你的数据库模式,高危语句有防护,错误信息清晰,支持 PostgreSQL 以及你使用的其他数据库。