This post originated from an RSS feed registered with Ruby Buzz by Paul Gross. Original Post: PostgreSQL allows duplicate nulls in unique columns Feed Title: Paul Gross's Blog - Home Feed URL: http://feeds.feedburner.com/pgrs Feed Description: Posts mainly about ruby on rails. Latest Ruby Buzz Posts Latest Ruby Buzz Posts by Paul Gross Latest Posts From Paul Gross's Blog - Home

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. Oracle behaves differently and will not allow the second insert of null.