when creating a index unique in a table that accept nulls the unique
constraint doesn't work. 

Example:
---
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" above insert sucefully.

Sun, 13 May 2001 00:07:11 +0200
Domingo Alvarez Duarte <domingo@dad-it.com>
bug in "create unique index"

This is correct by spec.  NULLs are a special case.

 From UNIQUE <table subquery) which unique constraints are defined
 against:
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.

[This means that there will be no two rows such that the value
of each column in non-null and is equal to to the value in the
other since one of the columns is null]

On Sun, 13 May 2001, Domingo Alvarez Duarte wrote:

> when creating a index unique in a table that accept nulls the unique
> constraint doesn't work. 
> 
> Example:
> ---
> 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" above insert sucefully.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 


Sat, 12 May 2001 20:50:39 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: bug in "create unique index"