Postgres如何避免含null字段的联合唯一约束产生重复行

问题说明 #

你现有public.users_types_brands表的三列联合唯一索引users_types_brands_users_types_id_brand_id_tasks_type_id_index无法限制tasks_type_id为NULL的重复行，是因为PostgreSQL遵循SQL标准：唯一约束中NULL会被判定为互不相等，因此相同users_types_id + brand_id组合可以重复插入tasks_type_id为NULL的记录，不符合业务要求。

实现方案 #

方案1：新增部分唯一索引（推荐） #

仅针对tasks_type_id为NULL的行做users_types_id + brand_id的唯一性校验，不影响原有非NULL值的校验逻辑，索引体积小、性能高：

CREATE UNIQUE INDEX idx_utb_utid_brand_null_tasks
ON users_types_brands (users_types_id, brand_id)
WHERE tasks_type_id IS NULL;

创建完成后，重复插入tasks_type_id为NULL的相同users_types_id + brand_id组合时，会直接触发唯一约束报错，完全符合业务要求。

方案2：表达式联合唯一索引 #

如果需要用单个索引同时覆盖NULL和非NULL的校验场景，可以用COALESCE函数将NULL替换为业务中不会使用的占位值（示例用-1，需提前确认该值不会出现在合法的tasks_type_id中）：

-- 先删除原有三列联合唯一索引
DROP INDEX users_types_brands_users_types_id_brand_id_tasks_type_id_index;
-- 新建基于表达式的联合唯一索引
CREATE UNIQUE INDEX users_types_brands_users_types_id_brand_id_tasks_type_id_index
ON users_types_brands (users_types_id, brand_id, COALESCE(tasks_type_id, -1));

前置操作 #

创建上述任意索引前，需要先清理表中已存在的重复数据，否则索引会创建失败，清理参考语句：

DELETE FROM users_types_brands
WHERE id IN (
    -- 筛选出重复的NULL行，仅保留id最小的一条
    SELECT id
    FROM users_types_brands a
    WHERE tasks_type_id IS NULL
    AND EXISTS (
        SELECT 1 FROM users_types_brands b
        WHERE b.users_types_id = a.users_types_id
        AND b.brand_id = a.brand_id
        AND b.tasks_type_id IS NULL
        AND b.id < a.id
    )
);

内容的提问来源于stack exchange，提问作者manuel.menendez