Thread

1. bug in "create unique index"

   Domingo Alvarez Duarte <domingo@dad-it.com> — 2001-05-12T22:07:11Z

   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.
   
   

2. Re: bug in "create unique index"

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-05-13T03:50:39Z

   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)
   >