PostgreSQL多列唯一索引失效问题排查求助

核心问题：NULL值在唯一索引中的特殊处理 #

PostgreSQL 中，NULL 不等于任何值，包括另一个 NULL。你的唯一索引包含proxy_user_id字段，而重复数据里该字段值为NULL，数据库会把这些NULL视为不同的“值”，因此不会触发唯一索引的约束，导致重复数据被插入。

解决方案 #

1. 先清理现有重复数据 #

执行以下SQL删除重复行，保留每组重复数据中id最小的一行：

DELETE FROM day_off_movements
WHERE id NOT IN (
    SELECT MIN(id)
    FROM day_off_movements
    GROUP BY user_id, proxy_user_id, day_off_start_date, day_off_end_date
);

2. 修复唯一索引约束 #

根据业务需求选择以下方案之一：

方案A：给proxy_user_id设置非空约束或默认值

如果业务逻辑中proxy_user_id可以有默认值（比如无代理时用特定标识），可以修改表结构：

-- 先设置默认值（比如-1，需确保该值不在users表的id中）
ALTER TABLE day_off_movements ALTER COLUMN proxy_user_id SET DEFAULT -1;
-- 再设置非空约束
ALTER TABLE day_off_movements ALTER COLUMN proxy_user_id SET NOT NULL;

之后原有的唯一索引就能正常工作，阻止重复插入。

方案B：使用部分唯一索引

针对proxy_user_id为NULL和非NULL的情况分别创建唯一索引，覆盖所有场景：

-- 处理proxy_user_id为NULL的情况：仅用user_id、日期字段做唯一约束
CREATE UNIQUE INDEX idx_day_off_null_proxy 
ON day_off_movements (user_id, day_off_start_date, day_off_end_date) 
WHERE proxy_user_id IS NULL;

-- 处理proxy_user_id非NULL的情况：保留原有的多列唯一约束
CREATE UNIQUE INDEX idx_day_off_non_null_proxy 
ON day_off_movements (user_id, proxy_user_id, day_off_start_date, day_off_end_date) 
WHERE proxy_user_id IS NOT NULL;

方案C：用COALESCE转换NULL值

将NULL转换为一个不会与合法user_id冲突的固定值，统一纳入唯一索引判断：

-- 替换原唯一索引（先删除旧索引）
DROP INDEX IF EXISTS idx_day_off_all;
CREATE UNIQUE INDEX idx_day_off_all 
ON day_off_movements (user_id, COALESCE(proxy_user_id, -1), day_off_start_date, day_off_end_date);

注意：-1需要是一个不存在于users表id中的值，避免与真实代理用户冲突。

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