Postgres含NULL值多列唯一约束下UPSERT冲突不触发问题求解

问题根因 #

PostgreSQL遵循SQL标准，默认唯一约束中NULL值被判定为互不相等，因此当col3为NULL时，即使存在同col2且col3为NULL的记录，也不会触发冲突检测。

最优解决方案 #

方案1：PostgreSQL 15及以上版本（首推，无侵入性能最优） #

PG15开始原生支持NULLS NOT DISTINCT修饰符，可直接指定唯一约束中NULL值视为相等，完全匹配需求：

1. 先删除原有唯一约束

ALTER TABLE my_table DROP CONSTRAINT ux_my_table_unique;

2. 新建带NULLS NOT DISTINCT的唯一约束

ALTER TABLE my_table ADD CONSTRAINT ux_my_table_unique UNIQUE NULLS NOT DISTINCT (col2, col3);

修改完成后原有编写的UPSERT语句不需要做任何调整，col3为NULL时也能正常触发冲突更新逻辑。

方案2：PostgreSQL 14及更早版本（表达式唯一索引实现） #

如果使用的PG版本不支持NULLS NOT DISTINCT，可以通过表达式唯一索引将NULL转换为业务不存在的占位值实现：

1. 先删除原有唯一约束

ALTER TABLE my_table DROP CONSTRAINT ux_my_table_unique;

2. 新建表达式唯一索引，此处以-9999999作为real类型的占位值（请替换为业务中绝对不会出现的数值）

CREATE UNIQUE INDEX ux_my_table_unique ON my_table (col2, COALESCE(col3, '-9999999'::real));

3. 对应调整UPSERT语句的冲突检测规则，和索引表达式保持一致：

insert into my_table (col2, col3, col4) values (p_col2, p_col3, p_col4)
on conflict (col2, COALESCE(col3, '-9999999'::real)) do update set col4=excluded.col4;

不推荐方案：触发器实现 #

触发器方案需要自行编写冲突判断逻辑，性能弱于原生唯一约束/索引，且容易遗漏边界场景，仅在上述两种方案都无法使用时考虑。

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