【PostgreSQL 15】PostgreSQL 15对UNIQUE和NULL的改进

用一句话来总结这种改进就是：

?

1	支持唯一性约束和索引将null值视为相同的值。之前是将null值索引成不同的值，现在可以通过使用unique nulls not distinct创建约束，将null值视为相同的值。

 

两种unique风格

创建示例表：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

CREATE TABLE null_old_style (     id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,     val1 TEXT NOT NULL,     val2 TEXT NULL,     CONSTRAINT uq_val1_val2         UNIQUE (val1, val2) );   CREATE TABLE null_new_style (     id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,     val1 TEXT NOT NULL,     val2 TEXT NULL,     CONSTRAINT uq_val1_val2_new         UNIQUE NULLS NOT DISTINCT (val1, val2) );

 

支持的数据的变化

在postgresql 14中或更早的版本，唯一性约束将null与null视为是不相同的。

这与sql标准是相同的，简而言之，null表示unknown。因而，null值也就不违反唯一性约束。

可以通过插入五行相同的记录到表null_old_style

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

INSERT INTO null_old_style (val1, val2) SELECT 'Hello', NULL     FROM generate_series(1, 5) ;   SELECT * FROM null_old_style;   id|val1 |val2| --+-----+----+  1|Hello|    |  2|Hello|    |  3|Hello|    |  4|Hello|    |  5|Hello|    |

这个行为是有文档可查，符合预期的。

 

引入了nulls not distinct选项后，唯一性约束更加严格，不再支持多个null值。

?

1 2 3 4 5 6 7 8

INSERT INTO null_new_style (val1, val2) SELECT 'Hello', NULL;   SELECT * FROM null_new_style;   id|val1 |val2| --+-----+----+  1|Hello|    |

再想插入一个val1值为'Hello'，val2值为null的记录就会违反唯一性约束：

?

1 2 3 4 5

INSERT INTO null_new_style (val1, val2) SELECT 'Hello', NULL;   SQL Error [23505]: ERROR: duplicate key value violates unique constraint "uq_val1_val2_new"   Detail: Key (val1, val2)=(Hello, null) already exists.

当然将val1换成'Hello'之外的一个值，val2值为null就可以插入了：

?

1 2 3 4 5 6 7

INSERT INTO null_new_style (val1, val2) SELECT 'World', NULL;   id|val1 |val2| --+-----+----+  1|Hello|    |  3|World|    |