Thread

1. create unique index doesn't work properly

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

   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.
   
   

2. Re: create unique index doesn't work properly

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-13T02:03:28Z

   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