Re: create unique index doesn't work properly

Domingo Alvarez Duarte <domingo@dad-it.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