为什么Postgres在涉及唯一约束的地方处理null不一致?

Question

我最近注意到Postgres在具有唯一约束的列中处理null的方式不一致。

考虑一张人的桌子:

create table People (
   pid  int  not null,
   name text not null,
   SSN  text unique,
 primary key (pid)
);

SSN列应该保持唯一。我们可以检查:

-- Add a row.
insert into People(pid, name, SSN)
values(0, 'Bob', '123');
-- Test the unique constraint.
insert into People(pid, name, SSN)
values(1, 'Carol', '123');

第二次插入失败，因为它违反了SSN上的惟一约束。到目前为止，一切顺利。但是让我们试试NULL:

insert into People(pid, name, SSN)
values(1, 'Carol', null);

。

select *    
from People;
0;"Bob";"123"
1;"Carol";"<NULL>"

唯一列的值为null。有趣。Postgres如何断言null是唯一的，或者不是唯一的呢?

我想知道我是否可以在一个唯一的列中添加两行null。

insert into People(pid, name, SSN)
values(2, 'Ted', null);
select *    
from People;
0;"Bob";"123"
1;"Carol";"<NULL>"
2;"Ted";"<NULL>"

我可以。现在在SSN列中有两行为NULL，尽管SSN应该是唯一的。

Postgres文档说，为了唯一约束的目的，空值不被认为是相等的。

好吧。我明白这一点。这是null处理中的一个微妙之处:通过考虑唯一约束列中的所有null是不相交的，我们延迟了唯一约束的实施，直到有一个实际的非空值作为实施的基础。

那很酷。但这就是Postgres让我迷惑的地方。如文档所述，如果唯一约束列中的所有null不相等，那么我们应该在select distinct查询中看到所有null。

select distinct SSN
from People;
"<NULL>"
"123"

不。这里只有一个空。似乎Postgres错了。但我想知道:有没有别的解释?

---

编辑:

Postgres文档确实在SELECT DISTINCT章节中指定了"Null值在此比较中被认为是相等的"。虽然我不理解这个概念，但我很高兴它在文档中得到了阐明。

Answer 1

在处理null时说:

几乎总是错误的

"null在这里表现得像某某，*所以它们应该表现得像某某here"

这是一篇从postgres角度关于这个主题的优秀文章。简而言之，根据上下文对null的处理方式不同，不要错误地对它们进行任何假设。

Answer 2

最重要的是，PostgreSQL对null的处理是因为SQL标准是这样规定的。

null显然很棘手，可以用多种方式解释(未知值、缺席值等)，因此在最初编写SQL标准时，作者不得不在某些地方进行一些调用。我想说，时间已经证明了他们或多或少是正确的，但这并不意味着不可能有另一种数据库语言处理未知和缺失的值稍微(或完全)不同。但是PostgreSQL实现了SQL，所以就是这样。

正如在另一个答案中已经提到的，Jeff Davis写了一些关于处理null的好文章和演讲。

Answer 3

NULL被认为是唯一的，因为NULL不代表没有值。列中的NULL为未知值。当你比较两个未知数时，你不知道它们是否相等，因为你不知道它们是什么。

想象一下，你有两个标有A和b的盒子。如果你不打开盒子，你就看不到里面，你永远不知道里面是什么。如果你问"这两个盒子里的东西一样吗?"你只能回答"我不知道"。

在这种情况下，PostgreSQL也会做同样的事情。当被要求比较两个NULL时，它会说"我不知道"。这与SQL数据库中NULL的疯狂语义有很大关系。在另一个答案中链接到的文章是理解NULL的行为的一个很好的起点。只是要注意:不同的供应商会有不同。

Answer 4

唯一索引中的多个NULL值是可以的，因为x = NULL对于所有x都是false，特别是当x本身为NULL时。在WHERE子句中，您也会遇到这种行为，您必须说WHERE x IS NULL和WHERE x IS NOT NULL，而不是WHERE x = NULL和WHERE x <> NULL。