[译]PostgreSQL15改进了UNIQUE和NULL

yanzongshuaiDBA 2022-07-16

580

PostgreSQL15改进了UNIQUE和NULL

最近发布了PG15 beta 2。本文关注对有NULL值的列进行UNIQUE约束的改进。虽然唯一约束的细小差别不如加速排序那样惊艳，但对于提高数据库开发人员对数据质量的控制来说，总归是一个好处。

邮件列表对此进行了讨论：

https://www.postgresql.org/message-id/flat/84e5ee1b-387e-9a54-c326-9082674bde78%40enterprisedb.com

PG15的release notes中指出：“允许唯一约束和索引将NULL值看作不同的值。以前NULL值总是被索引认为是不同的值，但现在可以通过使用UNIQUE NULLS NOT DISTINCT创建约束和索引来改变。”

UNIQUE的两种风格

创建2个表来了解这方面的意义。null_old_style表有两个列(val1,val2)上的UNIQUE约束。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)
);

允许插入数据的变化

PG14及之前版本，唯一约束将NULL值看作和其他NULL值不相等，PG14手册“当索引声明为唯一时，不允许具有相同索引值的多个行。NULL值视为不相等”。

这与SQL标准的处理方式是一致的。一般情况下，NULL是未知的，不可能确定一个未知数是否等于另一个未知数，并没有违反UNIQUE约束。向null_old_style表插入5行：

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

尝试再插入一行：

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改变一下，就可以插入了：

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


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

这样就符合我心里预期了。

总结

很高兴看到PG15中新增UNIQUE NULLS NOT DISTINCT语法。增加了数据指令控制级别。这也是一个影响较低的更新，默认操作照常使用。

原文

https://blog.rustprooflabs.com/2022/07/postgres-15-unique-improvement-with-null

select

文章转载自yanzongshuaiDBA，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

[译]PostgreSQL15改进了UNIQUE和NULL

UNIQUE的两种风格

允许插入数据的变化

总结

原文

评论