PostgreSQL 14.9创建可延迟且识别空值的唯一约束方案

核心方案：函数转换+可延迟唯一约束 #

通过将deleted列的null值转换为一个实际数据中不会出现的固定占位符，再基于转换后的字段创建可延迟的唯一约束，同时满足"null视为相等"和"事务提交时校验"的需求。

具体实现（迁移脚本示例） #

假设你的表名为your_table，name是字符串类型，deleted是可空的时间戳类型，迁移脚本中的SQL如下：

-- 创建基础表
CREATE TABLE your_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    deleted TIMESTAMP
);

-- 添加可延迟的唯一约束，将null转换为固定占位符
ALTER TABLE your_table
ADD CONSTRAINT unique_name_deleted
UNIQUE (name, COALESCE(deleted, '1970-01-01 00:00:00'::TIMESTAMP))
DEFERRABLE INITIALLY DEFERRED;

针对不同deleted类型的调整 #

• 如果deleted是布尔型：将占位符转换为字符串避免冲突，比如：

  UNIQUE (name, COALESCE(deleted::TEXT, '__NULL_BOOL__'))
  

• 如果deleted是UUID类型：用一个固定的无效UUID作为占位符：

  UNIQUE (name, COALESCE(deleted, '00000000-0000-0000-0000-000000000000'::UUID))
  

方案原理 #

1. Null值统一处理：COALESCE函数将所有deleted为null的记录转换为同一个占位符，让PostgreSQL将这些记录视为具有相同的deleted值，从而触发唯一约束，避免同name下多条null记录。
2. 延迟约束校验：DEFERRABLE INITIALLY DEFERRED配置让数据库在事务提交时才检查约束，而非每次更新后立即校验，解决了事务内交换deleted值时的冲突问题。
3. JPA兼容性：约束完全在数据库层面配置，Spring Data JPA/Hibernate无需感知函数转换逻辑，实体类只需正常映射name和deleted字段即可。

为什么不选其他方案？ #

• 双部分索引：无法支持延迟校验，事务内更新会触发即时冲突，不符合需求。
• 原生可延迟唯一约束：PostgreSQL默认不将null视为相等，无法限制同name下的多条null记录。
• PostgreSQL 15 DISTINCT NOT NULL：你的版本是14.9，不支持该特性。

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