Thread: BUG #7501: Unique index fails to check uniqueness on nulls

The following bug has been logged on the website:

Bug reference:      7501
Logged by:          Matt Kusnierz
Email address:      matthew.p.kusnierz@jpmorgan.com
PostgreSQL version: 9.0.4
Operating system:   Windows XP
Description:        =


Multi-column unique indexes allow duplicate rows to be inserted if any of
the indexed columns are inserted with a null.

For example:

create table foo (a varchar(10) null, b varchar(10) null);
create unique index uix on foo (a, b);

insert into foo (a, b) values ('abc', null);
insert into foo (a, b) values ('abc', null);

Clearly the second insert should fail with an ERROR: duplicate key value
violates unique constraint "uix"

But this does not occur!

On Thu, 2012-08-23 at 09:38 +0000, matthew.p.kusnierz@jpmorgan.com
wrote:
> Multi-column unique indexes allow duplicate rows to be inserted if any of
> the indexed columns are inserted with a null.

That is mandated in the SQL spec, and is documented here:

"For the purpose of a unique constraint, null values are not considered
equal."

 -- http://www.postgresql.org/docs/9.2/static/sql-createtable.html

NULLs can be confusing, I know.

Regards,
    Jeff Davis