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

i noticed inconsistency in how postgres handles nulls in columns unique constraint.

consider table of people:

create table people (    pid  int  not null,    name text not null,    ssn  text unique,  primary key (pid) ); 

the ssn column should kept unique. can check that:

-- add row. insert people(pid, name, ssn) values(0, 'bob', '123');  -- test unique constraint. insert people(pid, name, ssn) values(1, 'carol', '123'); 

the second insert fails because violates unique constraint on ssn. far, good. let's try null:

insert people(pid, name, ssn) values(1, 'carol', null); 

that works.

select *     people;  0;"bob";"123" 1;"carol";"<null>" 

a unique column take null. interesting. how can postgres assert null in way unique, or not unique matter?

i wonder if can add 2 rows null in unique column.

insert people(pid, name, ssn) values(2, 'ted', null);  select *     people;  0;"bob";"123" 1;"carol";"<null>" 2;"ted";"<null>" 

yes can. there 2 rows null in ssn column though ssn supposed unique.

the postgres documentation says, for purpose of unique constraint, null values not considered equal.

okay. can see point of this. it's nice subtlety in null-handling: considering nulls in unique-constrained column disjoint, delay unique constraint enforcement until there actual non-null value on base enforcement.

that's pretty cool. here's postgres loses me. if nulls in unique-constrained column not equal, documentation says, should see of nulls in select distinct query.

select distinct ssn people;  "<null>" "123" 

nope. there's single null there. seems postgres has wrong. wonder: there explanation?

---

edit:

the postgres docs specify "null values considered equal in comparison." in section on select distinct. while not understand notion, i'm glad it's spelled out in docs.

it mistake when dealing null say:

"nulls behave so-and-so here, *so should behave such-and-such here"

here excellent essay on subject postgres perspective. briefly summed saying nulls treated differently depending on context , don't make mistake of making assumptions them.