[译文] Postgres 15 改进了 UNIQUE 和 NULL

Postgres 15 beta 2 最近发布了！审查和测试新功能是一种有趣的日常任务。这篇文章着眼于对具有值UNIQUE的列的约束的改进。虽然唯一约束的细微差别不像使排序更快（这很令人兴奋！）那么华丽，但提高数据库开发人员对数据质量的控制总是一个很好的好处。

Postgres 15 发行说明 总结了这一改进：

“允许唯一约束和索引将 NULL 值视为不同的 (Peter Eisentraut)

以前NULL的值总是被索引为不同的值，但现在可以通过使用创建约束和索引来更改UNIQUE NULLS NOT DISTINCT。”

两种风格UNIQUE

为了了解此更改的作用，我们创建了两个表。该null_old_style表在 上具有 2 列UNIQUE约束(val1, val2)。val2允许NULL值。

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)
);

该null_new_style表使用新选项：UNIQUE NULLS NOT DISTINCT. 与上表的唯一区别是为唯一约束添加了新语法。

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)
);

允许数据的变化

在 Postgres 14 和之前的版本中，唯一约束将NULL值视为不等于其他NULL值。来自 Postgres 14 文档：

当索引声明为唯一时，不允许具有相同索引值的多个表行。空值不被视为相等。

这与 SQL 标准的处理方式是一致的NULL，一般情况NULL是未知的。不可能确定一个未知数是否等于另一个未知数。因为NULL值之间是未知相等的，所以它们不违反UNIQUE约束。这通过向表中添加 5 个相同的行来说明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|    |

此行为已记录在案并符合预期，甚至是 ANSI SQL 标准的一部分。也就是说，我从来没有真正喜欢过上述行为，因为它不够严格。

使用新NULLS NOT DISTINCT选项，唯一约束因不允许重复NULL值而更具限制性。添加一行开始。

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

SELECT * FROM null_new_style;

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

现在尝试使用'Hello'inval1和NULLin添加第二行val2 会导致违反唯一约束。

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为新值将允许NULL在中添加另一个值val2。

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

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

这符合我认为唯一约束应该如何与空值一起使用的心理模型。

概括

我很高兴看到UNIQUE NULLS NOT DISTINCTPostgres 15 中的新增功能。这为 Postgres 增加了额外的质量控制级别。这也是一个低影响的更改，因为默认操作将照常继续。

作者：Ryan Lambert

文章来源：https://blog.rustprooflabs.com/2022/07/postgres-15-unique-improvement-with-null