Thread

1. unique key and nulls

   Patrick Welche <prlw1@newn.cam.ac.uk> — 2006-12-05T18:06:18Z

   According to http://www.postgresql.org/docs/8.1/static/ddl-constraints.html:
   
      In general, a unique constraint is violated when there are two or more
      rows in the table where the values of all of the columns included in
      the constraint are equal. However, null values are not considered
      equal in this comparison. That means even in the presence of a unique 
      constraint it is possible to store duplicate rows that contain a null
      value in at least one of the constrained columns.
   
   So, from the above, I thought I could create a unique constraint on a table
   with unique values and nulls:
   
   patrimoine=# alter table socket add unique(port_id);
   NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "socket_port_id_key" for table "socket"
   ERROR:  could not create unique index
   DETAIL:  Table contains duplicated values.
   patrimoine=# select port_id,count(id) from socket group by port_id having count(id)>2;
    port_id | count 
   ---------+-------
            |   477
   (1 row)
   
   patrimoine=# select coalesce(999,port_id),count(id) from socket group by port_id having count(id)>2;
    coalesce | count 
   ----------+-------
         999 |   477
   (1 row)
   
   patrimoine=# select count(*) from socket where port_id is null;
    count 
   -------
      477
   (1 row)
   
   but with postgresql-head of 21st November 2006, it doesn't possible - am I
   missing something?
   
   (port_id is an integer, which already has the constraint
       "socket_port_id_fkey" FOREIGN KEY (port_id) REFERENCES port(id) MATCH FULL ON DELETE RESTRICT
   )
   
   Cheers,
   
   Patrick
   
   

2. Re: unique key and nulls

   Tom Lane <tgl@sss.pgh.pa.us> — 2006-12-05T18:30:17Z

   Patrick Welche <prlw1@newn.cam.ac.uk> writes:
   > patrimoine=# alter table socket add unique(port_id);
   > NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "socket_port_id_key" for table "socket"
   > ERROR:  could not create unique index
   > DETAIL:  Table contains duplicated values.
   > patrimoine=# select port_id,count(id) from socket group by port_id having count(id)>2;
   
   count(id)>1 would be the appropriate check, no?  Or really count(*)>1
   ... the above will give misleading answers if id can be null.
   
   			regards, tom lane