Why does Postgres handle NULLs inconsistently where unique constraints are involved?

Question

I recently noticed an inconsistency in how Postgres handles NULLs in columns with a unique constraint.

Consider a table of people:

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

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

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

That works.

select *    
from People;

0;"Bob";"123"
1;"Carol";"<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?

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

Yes I can. Now there are two rows with NULL in the SSN column even though SSN is supposed to be unique.

The Postgres documentation says, For the purpose of a unique constraint, null values are not considered equal.

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.

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"

Nope. There's only a single null there. It seems like Postgres has this wrong. But I wonder: Is there another explanation?

---

Edit:

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.

Answer 1

It is almost always a mistake when dealing with null to say:

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

Answer 2

The bottom line is, PostgreSQL does what it does with nulls because the SQL standard says so.

Nulls are obviously tricky and can be interpreted in multiple ways (unknown value, absent value, etc.), and so when the SQL standard was initially written, the authors had to make some calls at certain places. I'd say time has proved them more or less right, but that doesn't mean that there couldn't be another database language that handles unknown and absent values slightly (or wildly) differently. But PostgreSQL implements SQL, so that's that.

As was already mentioned in a different answer, Jeff Davis has written some good articles and presentations on dealing with nulls.

Answer 3

NULL is considered to be unique because NULL doesn't represent the absence of a value. A NULL in a column is an unknown value. When you compare two unknowns, you don't know whether or not they are equal because you don't know what they are.

Imagine that you have two boxes marked A and B. If you don't open the boxes and you can't see inside, you never know what the contents are. If you're asked "Are the contents of these two boxes the same?" you can only answer "I don't know".

In this case, PostgreSQL will do the same thing. When asked to compare two NULLs, it says "I don't know." This has a lot to do with the crazy semantics around NULL in SQL databases. The article linked to in the other answer is an excellent starting point to understanding how NULLs behave. Just beware: it varies by vendor.

Answer 4

Multiple NULL values in a unique index are okay because x = NULL is false for all x and, in particular, when x is itself NULL. You'll also run into this behavior in WHERE clauses where you have to say WHERE x IS NULL and WHERE x IS NOT NULL rather than WHERE x = NULL and WHERE x <> NULL.