Postgres 9.6中jsonb_populate_recordset插入Null值绕过唯一约束的解决方法

这个坑我之前踩过！PostgreSQL遵循SQL标准，默认不会把NULL值判定为相等——所以哪怕你给city和address加了UNIQUE约束，多次插入这两个字段都是NULL的记录时，数据库会认为这些记录并不重复，直接放行。针对你用jsonb_populate_recordset批量插入的场景，有两种靠谱的解决方案：

方案1：使用部分唯一索引（Partial Unique Index） #

这是最精准、性能最优的方式，适合你只需要特定NULL组合唯一的场景（比如city和address同时为NULL时只能存一条）。

创建一个只针对目标NULL场景的唯一索引：

CREATE UNIQUE INDEX idx_locations_unique_null_city_address
ON locations (1)  -- 用常量1占位，因为索引必须包含列，不影响逻辑
WHERE city IS NULL AND address IS NULL;

这个索引只会对city和address都为NULL的记录生效，强制这类记录只能存在一条。如果你的业务需要其他NULL组合（比如只有city为NULL）也唯一，只要再创建对应的部分索引就行。

方案2：用COALESCE将NULL替换为特殊值，创建全局唯一约束 #

如果你的需求是所有字段组合（包含任意NULL的情况）都必须唯一，可以用COALESCE把NULL替换成一个业务中绝不会出现的特殊标记，再基于替换后的值创建UNIQUE约束：

ALTER TABLE locations ADD CONSTRAINT locations_unique_city_address
UNIQUE (
    COALESCE(city, '<<__SPECIAL_NULL_MARKER__>>'),
    COALESCE(address, '<<__SPECIAL_NULL_MARKER__>>')
);

这样一来，任何NULL值都会被替换成<<__SPECIAL_NULL_MARKER__>>，两个NULL的组合就会被判定为相等，触发唯一约束。注意一定要选一个绝对不会出现在业务数据里的标记，避免和正常值冲突。

针对jsonb_populate_recordset的说明 #

不管用哪种方案，约束都是作用在表层面的，和你用jsonb_populate_recordset批量插入的方式完全兼容——插入时只要违反约束，PostgreSQL就会抛出错误，阻止重复数据写入。

两种方案的对比：

• 部分唯一索引：更轻量，只针对特定场景生效，索引体积小，查询性能更好。
• COALESCE全局约束：一次搞定所有NULL组合的唯一性，但索引会包含所有替换后的值，体积相对大一些。

你可以根据自己的业务需求选对应的方法～

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