Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #2975: UNIQUE INDEX doesn't work

BUG #2975: UNIQUE INDEX doesn't work

From
"Steven"
Date:
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.

Re: BUG #2975: UNIQUE INDEX doesn't work

From
Bruce Momjian
Date:
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. +

Re: BUG #2975: UNIQUE INDEX doesn't work

From
Michael Fuhr
Date:
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

Re: BUG #2975: UNIQUE INDEX doesn't work

From
Michael Fuhr
Date:
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