23505 unique_violation

速览

23505 是 PostgreSQL 的 unique_violation 条件。当行、索引构建或逻辑应用发现某个值不能与正在执行的唯一性约束共存时，就会产生这个状态码。

第一步应保存完整的 ErrorResponse 以及失败的语句。最有用的字段是 C（SQLSTATE）、M（主消息）、D（detail），以及在服务器提供时的 s（schema）、t（table）和 n（constraint 或 index）。在 psycopg 中，这些字段位于 exc.sqlstate 和 exc.diag。对象字段属于线协议；它们不是 CSV 日志的标准列名，也不是 JSON 日志的标准键名。

恢复方式取决于错误发生的位置：

• 自动提交语句失败，但连接可以继续接收下一条命令。
• 显式事务中的语句失败后，事务进入中止状态。必须回滚整个事务，或回滚到保存点，然后才能继续发语句；否则 PostgreSQL 会返回 25P02。
• 延迟唯一约束可以暂时接受重复行，并在 COMMIT 时报告 23505。
• PL/pgSQL 的 EXCEPTION 块可以在子事务中捕获该冲突，但处理器必须足够窄，能够确认究竟是哪一个操作失败。

当前选定的公开运行记录按案例和目标覆盖 PostgreSQL 18.6 的 12 个独立通过案例，以及 PostgreSQL 10.21 的 11 个通过案例；NULLS NOT DISTINCT 在 PG10 标为不适用。完整运行仍为未被替代案例的来源；定向最终记录分别选择 DML 诊断、精确的 log_fields 关联、registry 片段和手动事务边界，不重复或覆盖这些案例。另有 PG14.24/PG15.19 的版本边界对照：PG14 的显式 UNIQUE NULLS NOT DISTINCT 只得到不支持语法的 42601，PG15 则在第二个 NULL 上实际得到 23505；该对照不计入上述基础数量。选定 run ID 和结构化观察保存在公开证据 JSON中；被替代的 summary 与原始 JSONL 仅保留为本地审计数据。

上面的生成锁定快照表示目录定义文件的版本覆盖。本页的可执行兼容性目标是 PostgreSQL 18.6 和 10.21；目录条目不声称 23505 是 PostgreSQL 10 才引入的。

含义与触发路径

SQLSTATE 目录把 23505 放在 Class 23 integrity_constraint_violation 下，条件名为 unique_violation。在普通 btree 路径中，PostgreSQL 检查索引项；如果冲突的已提交键或并发插入键不符合索引语义允许的条件，就会报告错误。主键也是唯一索引，因此重复主键值同样使用这个状态码。

同一个 SQLSTATE 可以描述多种机制：

1. 违反 DML 唯一约束或索引。 INSERT，以及改变键值的 UPDATE，都可能与唯一索引保护的已有行冲突。普通消息模板是 duplicate key value violates unique constraint "..."，可选的 detail 是 Key (...)=(...) already exists.。
2. 构建唯一索引。 在已有重复行的表上构建唯一索引时使用另一套模板：could not create unique index "..."，detail 为 Key (...)=(...) is duplicated.。这是索引构建失败，不是普通行插入消息。
3. 延迟约束。 使用 DEFERRABLE INITIALLY DEFERRED 时，重复值可以留在事务中，直到提交时检查约束。因此错误对应 COMMIT，失败的顶层提交会回滚该事务。
4. 逻辑复制应用。 PostgreSQL 18 会把应用冲突分类为 insert_exists 等；冲突报告器仍将相关的 insert、update 和 multiple-unique 路径映射到 23505。其消息形态和服务器日志上下文与客户端 btree 插入不同。本页有该路径的源码和文档证据，但当前运行批次没有建立 publisher/subscriber 拓扑。

状态码说明了条件所属的类别，但不能单独说明冲突是持久的业务重复、键选择竞态，还是维护操作失败。需要结合语句、schema、约束定义、事务上下文和并发活动作出判断。

下面的 SQL 代码块是与指定案例同一操作的说明性片段。权威可执行来源是 scripts/verify_cases.py 和 verify/cases/23505/cases.json；片段只省略临时 schema 命名和清理，并标明案例 ID，不构成第二套可运行案例定义。

报文与诊断字段

在 PostgreSQL 18.6 源码的 nbtinsert.c 普通路径中，服务器调用 BuildIndexValueDescription，报告唯一性错误，并附加表和约束身份。一次真实运行记录如下：

SQLSTATE: 23505
severity: ERROR
message_primary: duplicate key value violates unique constraint "users_email_key"
message_detail: Key (email)=(a@example.test) already exists.
schema_name: c23505_dml_unique_conflict
table_name: users
constraint_name: users_email_key
source: nbtinsert.c / _bt_check_unique

detail 不是必然存在的。如果调用者没有权限查看相关列，行级安全策略阻止描述，或者索引是表达式索引，PostgreSQL 可能省略键值。相同运行中的 INSERT-only 角色仍得到 SQLSTATE 和对象身份，但没有 message_detail。

协议字段定义在 Error and Notice Message Fields 中。应从驱动异常读取这些字段，不要从日志解析器推断。18.6 的日志配置文档描述了 CSV 的 sql_state_code、message、detail 等字段，以及 JSON 的 state_code、message、detail 等键；两种格式都没有把协议中的 constraint_name 定义为标准日志字段。定向 log_fields run 按同一后端 PID、模式/表、约束、主消息和 detail 将 collector 记录与驱动诊断逐项匹配：PostgreSQL 18.6 同时得到 CSV 和 JSON，PostgreSQL 10.21 得到 CSV。

索引构建报文明确不同：

SQLSTATE: 23505
message_primary: could not create unique index "idx_concurrent"
message_detail: Key (email)=(dup) is duplicated.
source: tuplesortvariants.c / comparetup_index_btree_tiebreak

不要只解析英文报文来分类错误。应先按 SQLSTATE 分支，再使用结构化字段和操作上下文。消息文本可能随本地化变化，而五字符 SQLSTATE 是稳定的线协议值。

诊断

记录失败语句、SQLSTATE、主消息、detail、hint、schema/table/constraint 字段、服务器版本和事务状态。驱动应保留原始异常；通用 ORM 错误字符串可能丢掉定位冲突对象所需的字段。

针对某个表，在修改数据前同时检查约束和索引。下面两条查询就是运行器使用的 diagnostic_catalog_queries；隔离运行会对其 accounts 表实际执行：

SELECT conname, contype, condeferrable, condeferred,
       pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'accounts'::regclass;

SELECT indexrelid::regclass AS index_name,
       indisunique, indisvalid, indisready, indislive,
       pg_get_indexdef(indexrelid)
FROM pg_index
WHERE indrelid = 'accounts'::regclass;

对于普通 DML 错误，将尝试写入的键与命名约束保护的行进行比较。检查该表上的每一项唯一性约束；语句指定了一个冲突目标，也仍可能违反另一个唯一约束。对于索引构建错误，在重试前找出重复键，并在并发构建失败后检查 pg_index。

运行案例 concurrent_unique_conflict 使用两个会话和一个观察会话。会话 A 持有未提交的 token='raced'；在 A 提交前，观察会话看到 B 的语句 wait_event_type=Lock、wait_event=transactionid。A 提交后，B 收到 23505，并在回滚前处于 INERROR。这个同步条件证明了事件顺序；固定 sleep 不能提供同等证据。

处置与修复

先恢复事务（案例：explicit_tx_abort_recovery、savepoint_recovery）

启用自动提交时，失败操作已经结束，运行案例中的连接状态为 IDLE。应用应先决定如何处理输入，再发起下一条命令。

在显式事务中，第一次失败后不要继续使用该连接，先处理失败事务：

下面的 INSERT INTO items VALUES (3, 'seed') 是真实的重复键触发；后面的 ROLLBACK 是必须的恢复操作。

CREATE TABLE items(id integer PRIMARY KEY, note text UNIQUE NOT NULL);
INSERT INTO items VALUES (1, 'seed');
BEGIN;
INSERT INTO items VALUES (2, 'outer');
INSERT INTO items VALUES (3, 'seed');
ROLLBACK;
BEGIN;
INSERT INTO items VALUES (2, 'after rollback');
COMMIT;

如果只有一小段工作是可选的，可以使用保存点保留外层工作：

保存点之后的插入复用了已经准备好的唯一值，因此是真实的 23505 触发；ROLLBACK TO SAVEPOINT 只撤销这段失败的子事务。

CREATE TABLE items(id integer PRIMARY KEY, note text UNIQUE NOT NULL);
INSERT INTO items VALUES (1, 'seed');
BEGIN;
INSERT INTO items VALUES (2, 'outer');
SAVEPOINT unique_case;
INSERT INTO items VALUES (3, 'seed');
-- 事务失败时，这条语句预期返回 25P02。
SELECT count(*) FROM items;
ROLLBACK TO SAVEPOINT unique_case;
INSERT INTO items VALUES (3, 'after savepoint');
RELEASE SAVEPOINT unique_case;
COMMIT;

运行器在回滚前执行 SELECT 时观察到 25P02，在 ROLLBACK TO SAVEPOINT 后观察到 INTRANS。普通 ROLLBACK 会让显式事务回到 IDLE；ROLLBACK TO 会保留保存点之前的工作。

延迟约束会改变错误发生的阶段（案例：deferred_commit_conflict）。在真实案例中，两次重复插入在连接处于 INTRANS 时都成功；随后 COMMIT 报 23505，连接回到 IDLE，失败顶层事务产生的行数为零。应在提交前修复键，或回滚并重试整个工作单元。

PL/pgSQL 可以在异常块中处理自然产生的唯一性冲突（案例：plpgsql_exception_recovery）：

CREATE TABLE items(id integer PRIMARY KEY, note text);
INSERT INTO items(id, note) VALUES (1, 'seed');

CREATE FUNCTION try_insert(wanted integer) RETURNS text
LANGUAGE plpgsql AS $$
DECLARE returned_state text;
BEGIN
    INSERT INTO items(id, note) VALUES (wanted, 'body');
    RETURN 'inserted';
EXCEPTION WHEN unique_violation THEN
    GET STACKED DIAGNOSTICS returned_state = RETURNED_SQLSTATE;
    INSERT INTO items(id, note) VALUES (wanted + 1, 'handler');
    RETURN returned_state;
END
$$;

SELECT try_insert(1);

受保护的代码块具有子事务行为。发生错误时，该代码块内部已经写入的持久化改动会在处理器运行前回滚；进入代码块之前的改动仍会保留。异常块应保持窄范围：如果其中有多条可能违反不同唯一约束的语句，那么捕获到 unique_violation 本身不能证明是哪一条操作造成了它。PostgreSQL 的 PL/pgSQL 文档也针对通用 upsert 处理器提醒了这一点。

选择符合业务语义的操作（案例：on_conflict_target_scope）

ON CONFLICT 用于表达明确的冲突策略，不是隐藏所有重复行的通用指令。冲突目标决定 arbiter。在运行案例中，已有行占用了 phone='phone-1'：

CREATE TABLE accounts(
    id integer PRIMARY KEY,
    email text NOT NULL,
    phone text NOT NULL,
    CONSTRAINT accounts_email_uq UNIQUE (email),
    CONSTRAINT accounts_phone_uq UNIQUE (phone)
);
INSERT INTO accounts VALUES (1, 'existing@example.test', 'phone-1');

INSERT INTO accounts(id,email,phone)
VALUES (2, 'existing@example.test', 'phone-2')
ON CONFLICT (email) DO NOTHING;

-- 只处理 email 冲突。仅 phone 冲突时仍会产生 23505。
INSERT INTO accounts(id,email,phone)
VALUES (3, 'new@example.test', 'phone-1')
ON CONFLICT (email) DO NOTHING;

-- 省略 target 时，DO NOTHING 覆盖任一可用 arbiter 的冲突。
INSERT INTO accounts(id,email,phone)
VALUES (4, 'third@example.test', 'phone-1')
ON CONFLICT DO NOTHING;

对于 DO UPDATE，应确保更新是确定性的，并检查其业务结果。对于幂等键，应将传入请求的相关身份和参数与已存请求比较，再核对已有业务结果，然后才能返回“已经处理”。仅发生幂等键碰撞，不能证明先前请求等价。

谨慎修复序列（案例：sequence_lag_repair）

手动指定键可能使序列落后于表。受控运行案例使用了非默认序列：

下面第二个使用 nextval 的插入是真实的重复键触发；setval 后又执行了一次真实插入，以验证修复后的下一个值。

CREATE SEQUENCE ids_seq START WITH 100 INCREMENT BY 7 MINVALUE 100 MAXVALUE 100000;
CREATE TABLE items(id integer PRIMARY KEY, note text);
INSERT INTO items(id,note) VALUES (100, 'explicit');
INSERT INTO items(id,note) VALUES (nextval('ids_seq'), 'generated');
SELECT setval('ids_seq', (SELECT max(id) FROM items), true);
INSERT INTO items(id,note) VALUES (nextval('ids_seq'), 'after repair');

受控 setval 后下一值为 107。这种修复有明确前提：暂停写入者，确认序列身份和所有关系，空表不能传入无效值，并检查 increment、边界、cache 和 is_called。setval 不是通用的并发修复；序列变更也不会像普通表写入那样回滚。

只有在操作可重试时才重试

序列化失败处理文档 说明了某些并发选择键的场景可能以 23505 呈现。应用确认属于这种语义后，应重试完整事务（包括选择键的逻辑），并配合有界退避和幂等策略。不要盲目重试：用户明确请求的重复可能是永久条件，反复尝试也可能得到同一冲突。

双会话案例证明了锁顺序建立后会出现冲突，但运行器没有声称所有相同状态码的业务操作都可以安全重试。

处理并发索引构建失败（案例：index_build_conflict）

CREATE INDEX 文档 说明，如果并发构建的扫描遇到唯一性失败等问题，可能留下 INVALID 索引。在本次重复扫描案例中，pg_index 显示 indisvalid=false、indisready=false、indislive=true、indisunique=true；普通事务性构建回滚后没有留下索引。解决重复数据后检查实际目录状态，适当时删除遗留的无效索引，再重新构建。不要把这一状态推广到 CREATE INDEX CONCURRENTLY 的所有失败阶段。

版本与边界

PostgreSQL 10.21 和 18.6 都实际观察到了相同 SQLSTATE。两版源码行号和内部函数名不同；兼容性判断应使用 SQLSTATE 与操作上下文，而不是使用源码行号。

UNIQUE NULLS NOT DISTINCT 在 PostgreSQL 15 及以后可用，且必须显式选择。运行案例向默认唯一列插入两个 NULL 成功；向单独声明的 UNIQUE NULLS NOT DISTINCT 约束插入第二个 NULL 时产生 23505。升级不会默默把旧约束的默认语义改成 NULLS NOT DISTINCT。

单独的版本边界对照已经实际记录了可用性和行为：PostgreSQL 14.24 接受普通唯一列的两个 NULL，但显式声明因 42601 拒绝且没有 23505；PostgreSQL 15.19 在显式约束的第二个 NULL 上产生 23505，自动提交会话保持 IDLE，并成功提交有效值修复。这些记录单独保留，不增加基础选定案例数量。在其他临时目标上执行时，应先检查版本并按分支运行：先执行 ordinary_create、ordinary_first、ordinary_second、ordinary_verify，再执行 explicit_create；如果得到 PG14 预期的 42601，就在这里停止，不要发送 explicit_first、explicit_second、explicit_repair 或 explicit_verify。只有 explicit_create 在 PG15 或更高版本成功后，才继续显式插入、观察第二个 NULL 的结果，再执行修复和验证。

-- ordinary_create
CREATE TABLE ordinary_nulls (external_id integer UNIQUE);
-- ordinary_first
INSERT INTO ordinary_nulls VALUES (NULL);
-- ordinary_second
INSERT INTO ordinary_nulls VALUES (NULL);
-- ordinary_verify
SELECT count(*) FROM ordinary_nulls;
-- explicit_create
CREATE TABLE explicit_nulls (external_id integer, CONSTRAINT nulls_not_distinct_uq UNIQUE NULLS NOT DISTINCT (external_id));
-- explicit_first
INSERT INTO explicit_nulls VALUES (NULL);
-- explicit_second
INSERT INTO explicit_nulls VALUES (NULL);
-- explicit_repair
INSERT INTO explicit_nulls VALUES (1);
-- explicit_verify
SELECT count(*) FROM explicit_nulls;

PostgreSQL 18 的逻辑冲突报告器使用 insert_exists 等标签。消息改变并不意味着相关唯一冲突换成了别的 SQLSTATE。本批证据来自源码和文档；运行报告没有声称测试了复制拓扑。

锁定目录在 PostgreSQL 7.4 的定义中已观察到 23505，并持续到 8.4.22 的 pre-9.0 定义；随后在列出的所有正式快照直到 PostgreSQL 18.6 以及 PostgreSQL 19 Beta 3 预览中存在。这是 definition_only 的存在边界，不是确切实现引入版本或运行时使用断言。

相关代码

• 25P02 — in_failed_sql_transaction 说明显式事务中未处理语句失败后的后续错误。
• 40001 — serialization_failure 与 40P01 — deadlock_detected 是主要的事务重试相关错误；PostgreSQL 也记录了特定的 23505 键选择竞态。
• 23503 — foreign_key_violation 也是 Class 23 完整性错误，但缺少被引用行的诊断和修复步骤不同。

来源与证据

• 核验材料 doc/src/sgml/ref/create_index.sgml REL_18_6 724edf9bd 3698f11c
• 核验材料 doc/src/sgml/ref/insert.sgml REL_18_6 724edf9bd f9340377
• 核验材料 doc/src/sgml/config.sgml PG18-docs 724edf9bd 19c5d112
• 核验材料 doc/src/sgml/mvcc.sgml PG18-docs 724edf9bd 4b7bf77e
• 核验材料 doc/src/sgml/plpgsql.sgml PG18-docs 724edf9bd 43d705ad
• 核验材料 doc/src/sgml/protocol.sgml PG18-docs 724edf9bd 2531c535
• 核验材料 doc/src/sgml/func.sgml PG18-docs 724edf9bd 05d03f50
• 核验材料 doc/src/sgml/xact.sgml PG18-docs 724edf9bd b48fbe8b
• 核验材料 src/backend/utils/errcodes.txt REL_18_6 724edf9bd 6e8de346
• 核验材料 src/backend/access/index/genam.c 第 155–275 行 REL_18_6 724edf9bd 11897b1d
• 核验材料 src/backend/replication/logical/conflict.c 第 102–180 行 REL_18_6 724edf9bd 1dc69598
• 核验材料 src/backend/access/nbtree/nbtinsert.c 第 640–674 行 REL_18_6 724edf9bd 199671be
• 核验材料 src/backend/utils/sort/tuplesortvariants.c 第 1670–1694 行 REL_18_6 724edf9bd f160e11e