PostgreSQL allows duplicate nulls in unique columns
It seems strange, but duplicate null values do not violate unique constraints in PostgreSQL.
Inserting the same non-null value twice in a unique column fails as expected:
# create table test ( a varchar unique ); # insert into test values(1); INSERT 0 1 # insert into test values(1); ERROR: duplicate key violates unique constraint "test_a_key"
However, the same is not true for null:
test=# insert into test values(null); INSERT 0 1 test=# insert into test values(null); INSERT 0 1 # select * from test; a --- 1 (3 rows)
I think this is misleading, but PostgreSQL says that it is following the SQL standard: Unique Constraints.
Update (1/16/08): Pramod Sadalage showed me that Oracle actually behaves just like PostgreSQL. I’m not sure why I was seeing different behavior, but I could not reproduce the problem.