PostgreSQL为含NULL值的personnel_id列添加唯一约束报错求助

问题场景 #

在PostgreSQL 15.3（Debian 15.3-1.pgdg120+1）环境下，尝试给myschema.mytable表中多数值为NULL的personnel_id列添加唯一约束，执行语句如下：

ALTER TABLE "myschema"."mytable" ADD UNIQUE NULLS not distinct ("personnel_id");

触发错误 #

执行后返回错误提示：

ERROR: could not create unique index "mytable_personnel_id_key"
DETAIL: Key (personnel_id)=() is duplicated.

原因说明 #

NULLS NOT DISTINCT规则会将所有NULL值视为完全相同的重复键，而你的表中存在多条personnel_id为NULL的记录，因此PostgreSQL判定存在重复键，无法创建唯一约束。

解决办法 #

方式1：清理重复NULL记录（业务允许时） #

删除多余的NULL记录，仅保留一条：

DELETE FROM "myschema"."mytable"
WHERE personnel_id IS NULL
AND ctid NOT IN (
    SELECT MIN(ctid)
    FROM "myschema"."mytable"
    WHERE personnel_id IS NULL
);

执行完删除操作后，重新运行添加约束的语句即可。

方式2：创建部分唯一索引（需保留所有NULL记录时） #

仅对非NULL的personnel_id值做唯一校验，NULL值不受限制：

CREATE UNIQUE INDEX idx_mytable_personnel_id_not_null ON "myschema"."mytable" (personnel_id)
WHERE personnel_id IS NOT NULL;

内容的提问来源于stack exchange，提问作者Zeinab Abbasimazar