PostgreSQL中含NULL的主键处理问题及解决方案咨询

针对你遇到的表结构调整问题，除了你提到的两种方案，还有以下几个可行的思路：

1. 单列主键+业务层面的部分唯一约束 #

用单列自增主键（比如id，GENERATED BY DEFAULT AS IDENTITY）作为表的主键，然后根据业务规则创建部分唯一索引来保证数据唯一性：

• 如果业务要求同一ukey+effective_date下，非NULL的settle_date必须唯一，且允许多条settle_date为NULL的记录：

  CREATE UNIQUE INDEX idx_unique_settle_notnull ON your_table (ukey, effective_date, settle_date) WHERE settle_date IS NOT NULL;
  

• 如果业务要求同一ukey+effective_date下只能有一条settle_date为NULL的记录：

  CREATE UNIQUE INDEX idx_unique_settle_null ON your_table (ukey, effective_date) WHERE settle_date IS NULL;
  CREATE UNIQUE INDEX idx_unique_settle_notnull ON your_table (ukey, effective_date, settle_date) WHERE settle_date IS NOT NULL;
  

这种方式既解决了settle_date为NULL的主键冲突问题，又能精准控制业务数据的唯一性规则，同时自增主键的批量插入性能可以通过调整序列缓存参数优化，不必过度担心INSERT操作量的问题。

2. 改进版的NULL替代值方案 #

你提到的填充虚拟日期方案可以优化：选择数据库支持的边界日期值（比如'0001-01-01'而不是'2999-12-31'），并在业务层统一处理该值的显示逻辑（比如查询时自动将该值转为NULL），然后将(ukey, effective_date, settle_date)设为复合主键。
优点是保留了原表的复合主键设计思路，缺点是需要业务层配合处理标记值，避免业务逻辑混淆。

3. UUID单列主键 #

用UUID作为表的单列主键（比如id UUID DEFAULT gen_random_uuid()），再搭配上述的业务唯一约束。这种方案适合分布式系统场景，不需要依赖数据库的自增序列，批量插入时也不会有序列竞争问题，唯一的缺点是UUID比自增ID占用更多存储空间，索引性能略低。

对现有方案的补充说明 #

• 关于填充虚拟日期：如果业务中存在日期范围查询，需要额外排除虚拟值，容易引发逻辑漏洞，建议谨慎使用；
• 关于自增序列主键：只要合理设置序列的缓存大小（比如GENERATED BY DEFAULT AS IDENTITY (CACHE 1000)），即使大量INSERT操作也能保证性能，是工业界的常用方案。

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