Re: create unique index doesn't work properly

Domingo Alvarez Duarte <domingo(at)dad-it(dot)com> writes:
> when executing the code bellow:

> create table test_unique(i1 integer, i2 integer, unique(i1,i2));
> insert into test_unique(1,null);
> insert into test_unique(1,null);
> insert into test_unique(1,null);

> all "inserts" terminate sucefully, if there isn't "null" values it works
> fine.

This is not a bug: the UNIQUE test is operating according to the SQL
standard. I direct your attention to section 8.9 of the SQL92 spec
(which is talking about the UNIQUE predicate, but a UNIQUE constraint
on a table is elsewhere defined in terms of the UNIQUE predicate):

<unique predicate> ::= UNIQUE <table subquery>

1) Let T be the result of the <table subquery>.

2) If there are no two rows in T such that the value of each column
in one row is non-null and is equal to the value of the cor-
responding column in the other row according to Subclause 8.2,
"<comparison predicate>", then the result of the <unique predi-
cate> is true; otherwise, the result of the <unique predicate>
is false.

A row containing any nulls cannot cause the UNIQUE test to fail.
Therefore, you can have any number of NULL rows in a "unique" column.
(If you don't like this, consider adding NOT NULL constraints to your
table.)

There are some well-known commercial DBMSes that get this wrong. But
Postgres is following the spec.

regards, tom lane