PostgreSQL违反部分唯一索引约束仍可插入问题求助

你遇到的问题核心在于唯一索引的列包含了deleted_at，而PostgreSQL对NULL值的处理逻辑导致了这个意外：

在PostgreSQL的唯一约束（包括唯一索引）中，NULL被视为“不相等”的。你创建的索引是UNIQUE, btree (account_id, state_id, deleted_at) WHERE deleted_at IS NULL，这意味着索引里存储的条目是(account_id, state_id, NULL)。因为NULL不等于NULL，所以多条这样的条目会被索引判定为不同，自然不会触发唯一约束，导致重复数据被插入。

正确的做法 #

你需要创建的是仅包含account_id和state_id的唯一部分索引，把deleted_at IS NULL作为过滤条件即可，不需要将deleted_at加入索引列：

CREATE UNIQUE INDEX account_id_state_id_uniq_index ON application.billing_infos (account_id, state_id) WHERE deleted_at IS NULL;

这样设置后，当deleted_at为NULL时，account_id和state_id的组合会被强制唯一，插入重复数据时就会触发唯一约束报错，符合你的预期。

补充说明 #

部分唯一索引的过滤条件只是用来筛选哪些行需要被纳入索引范围，而唯一约束的判定是基于索引列的组合。你之前的写法错误地把过滤字段加入了索引列，反而利用了PostgreSQL对NULL的特殊处理，导致约束失效。

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