空值不唯一 [英] Null value isn't unique

问题描述

我在postgres上有这样的行：

I have rows like these on postgres:

 name | address | college 

 john | rome    |
 john | rome    |
 max  | tokyo   |

我创建一个这样的表：

create test (
name    varchar(10),
address varchar(20),
college varchar(20),
constraint test_uq unique (name,address,college);

如何使空值变得唯一，如下：

How can I make null values become unique, so the output can be like this:

 name | address | college 

 john | rome    |
 max  | tokyo   |

推荐答案

Postgres documentation 声明此行为符合SQL标准：

Postgres documentation claims that this behaviour is compliant with the SQL standard:

一般来说，当表中有两个或多个
行时，会违反唯一约束，其中包含在
中的所有列的值但是，在这个比较中，两个空值不被认为是
，这意味着即使存在唯一的
约束，也可以存储包含空值的重复行
值中的至少一个。这种行为
符合SQL标准[。]

In general, a unique constraint is violated when there are two or more rows in the table where the values of all of the columns included in the constraint are equal. However, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard[.]

一种可能性是重新思考你的模式 name + address + college 的唯一性约束在您的示例中没有什么意义）。

One possibility is to rethink your schema (to be honest, a uniqueness constraint on name+address+college doesn't make a whole lots of sense in your example).

这篇关于空值不唯一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！