unique key and nulls

From: Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)uk>
To: pgsql-hackers(at)postgresql(dot)org
Subject: unique key and nulls
Date: 2006-12-05 18:06:18
Message-ID: 20061205180618.GQ7656@quartz.itdept.newn.cam.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2006-12-05 18:06:35 Re: Weak passwords and brute force attacks
Previous Message Gregory Stark 2006-12-05 17:59:54 Re: old synchronized scan patch