unique key and nulls

Started by Patrick Welcheabout 19 years ago2 messages
#1Patrick Welche
prlw1@newn.cam.ac.uk

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Patrick Welche (#1)
Re: unique key and nulls

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