BUG #2975: UNIQUE INDEX doesn't work

Steven wrote:

The following bug has been logged online:

Bug reference: 2975
Logged by: Steven
Email address: s.lambert@madeware.nl
PostgreSQL version: 8.1.4
Operating system: Windows Xp Sp2
Description: UNIQUE INDEX doesn't work
Details:

I have a unique index on a table, but it is still possible to insert a
duplicated row, without any message what so ever.

Please show us a self-contained example.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Thu, Feb 08, 2007 at 11:57:19AM -0500, Bruce Momjian wrote:

Steven wrote:

I have a unique index on a table, but it is still possible to insert a
duplicated row, without any message what so ever.

Please show us a self-contained example.

Are you perchance inserting NULL into the column(s) with the unique
index?

--
Michael Fuhr

On Wed, Feb 14, 2007 at 10:01:58AM +0100, Steven Lambert wrote:

You are right, the index has more columns and one of them have most of the
time the value NULL.
It is the date when the record has been "deleted"

See "Unique Constraints" in the documentation, in particular the
last paragraph:

http://www.postgresql.org/docs/8.1/interactive/ddl-constraints.html#AEN2016

"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."

This has been discussed before; search the archives for words like
"null", "unique", and "comparison" or "compare".

--
Michael Fuhr