为什么在涉及唯一约束的地方，Postgres不一致地处理NULL？ [英] Why does Postgres handle NULLs inconsistently where unique constraints are involved?
问题描述
我最近注意到Postgres如何处理具有唯一约束的列中的NULL不一致。
I recently noticed an inconsistency in how Postgres handles NULLs in columns with a unique constraint.
考虑一个人表：
create table People (
pid int not null,
name text not null,
SSN text unique,
primary key (pid)
);
SSN列应保持唯一。我们可以检查以下内容：
The SSN column should be kept unique. We can check that:
-- 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：
The second insert fails because it violates the unique constraint on SSN. So far, so good. But let's try a NULL:
insert into People(pid, name, SSN)
values(1, 'Carol', null);
可行。
select *
from People;
0;"Bob";"123"
1;"Carol";"<NULL>"
唯一列将为空。有趣。 Postgres如何断言null在任何情况下都是唯一的，或者不是唯一的？
A unique column will take a null. Interesting. How can Postgres assert that null is in any way unique, or not unique for that matter?
我想知道是否可以在唯一列中添加两行带有null的行。
I wonder if I can add two rows with null in a unique column.
insert into People(pid, name, SSN)
values(2, 'Ted', null);
select *
from People;
0;"Bob";"123"
1;"Carol";"<NULL>"
2;"Ted";"<NULL>"
是的。现在，即使SSN应该是唯一的，在SSN列中也有两行为NULL。
Yes I can. Now there are two rows with NULL in the SSN column even though SSN is supposed to be unique.
Postgres文档说，出于唯一约束的目的，null值不视为相等。
好的。我明白这一点。在null处理方面，这是一个很好的微妙之处：通过考虑唯一约束列中的所有NULL是不相交的，我们将唯一约束强制执行延迟到有一个实际的非null值作为该强制执行的基础。
Okay. I can see the point of this. It's a nice subtlety in null-handling: By considering all NULLs in a unique-constrained column to be disjoint, we delay the unique constraint enforcement until there is an actual non-null value on which to base that enforcement.
这很酷。但是，这里是Postgres迷失我的地方。如文档所述，如果唯一约束列中的所有NULL都不相等，那么我们应该在 select different 查询中看到所有的NULL。
That's pretty cool. But here's where Postgres loses me. If all NULLs in a unique-constrained column are not equal, as the documentation says, then we should see all of the nulls in a select distinct query.
select distinct SSN
from People;
"<NULL>"
"123"
不是。那里只有一个空值。似乎Postgres有这个错误。但我想知道：还有其他解释吗？
Nope. There's only a single null there. It seems like Postgres has this wrong. But I wonder: Is there another explanation?
编辑：
Postgres文档确实指定空值在此比较中被视为相等。 SELECT DISTINCT上部分中。虽然我不理解该概念，但我很高兴在文档中对它进行了说明。
The Postgres docs do specify that "Null values are considered equal in this comparison." in the section on SELECT DISTINCT. While I do not understand that notion, I'm glad it's spelled out in the docs.
推荐答案
几乎总是一个错误当处理 null 时说：
It is almost always a mistake when dealing with null to say:
null的行为就像所以在这里，*所以它们应该表现得像
这样……
"nulls behave like so-and-so here, *so they should behave like such-and-such here"
此处是关于该主题的出色论文。简短地总结一下，说空值根据上下文的不同而有所区别，并且不要犯任何对它们的假设的错误。
Here is an excellent essay on the subject from a postgres perspective. Briefly summed up by saying nulls are treated differently depending on the context and don't make the mistake of making any assumptions about them.
这篇关于为什么在涉及唯一约束的地方，Postgres不一致地处理NULL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
