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

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

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

 

两种unique风格

创建示例表：

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

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值。

INSERT INTO null_new_style (val1, val2)
SELECT 'Hello', NULL;

SELECT * FROM null_new_style;

id|val1 |val2|
--+-----+----+
 1|Hello|    |

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

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就可以插入了：

INSERT INTO null_new_style (val1, val2)
SELECT 'World', NULL;

id|val1 |val2|
--+-----+----+
 1|Hello|    |
 3|World|    |