如何实现PostgreSQL中含NULL列的三列特殊唯一索引

要实现你需要的约束，需要结合唯一部分索引和**排除约束（EXCLUDE CONSTRAINT）**来完成，具体步骤如下：

1. 创建非NULL行的唯一约束 #

保留你原来的部分唯一索引，确保当building不为NULL时，town、street、building三者组合唯一：

CREATE UNIQUE INDEX "three_cols" 
ON "users" ("town", "street", "building") 
WHERE "building" IS NOT NULL;

2. 添加排除约束禁止NULL行与同town+street的其他行共存 #

添加一个排除约束，确保只要存在building为NULL的行，就不允许任何同town、street的行（无论building是否为NULL）存在：

ALTER TABLE "users" ADD CONSTRAINT "exclude_town_street_with_null"
EXCLUDE USING btree ("town" WITH =, "street" WITH =)
WHERE ("building" IS NULL);

约束作用说明 #

• 这个排除约束会阻止任意两行满足：其中一行的building为NULL，且两行的town和street完全相等。
• 既禁止插入多个同town+street的NULL行，也禁止在已有NULL行的情况下插入任何同town+street的非NULL行。

验证场景 #

允许的插入 #

1. 插入多个同town+street但building不同的非NULL行：

   INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', '8');
   INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', '9');
   

   （通过，three_cols索引确保三者组合唯一，无冲突）

2. 插入单个同town+street的NULL行：

   INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', null);
   

   （通过，排除约束未检测到同town+street的其他行）

不允许的插入 #

1. 已有非NULL行时插入NULL行：

   INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', '8');
   INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', '9');
   INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', null);
   

   （失败，排除约束检测到已有同town+street的行，阻止插入）

2. 插入重复的town+street+building组合：

   INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', '8');
   INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', '8');
   

   （失败，three_cols索引触发唯一冲突）

3. 插入多个同town+street的NULL行：

   INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', null);
   INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', null);
   

   （失败，排除约束阻止两行的town+street相等且其中一行building为NULL）

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