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
thecolumns included in the constraint are equal. However, null values are not considered equal in this comparison.
Thatmeans even in the presence of a unique constraint it is possible to store duplicate rows that contain a null
valuein 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