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
