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.

Update (11/14/23): PostgreSQL now has a feature to change this behavior: Two Exciting PostgreSQL Features To Improve NULL Handling