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.