Re: Duplicate records in table

From: John Burger <john(at)mitre(dot)org>
To: Vinod Viswanathan <vinodv(at)optena(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Duplicate records in table
Date: 2005-04-11 03:37:36
Message-ID: e06cb72f2bda5c5866af4425c8a54722@mitre.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Vinod Viswanathan wrote:

> I have a table with an btree unique index over 4 varchar keys.
> When I insert and if one or more keys is null, postgres allows
> duplicate rows to be inserted into the table.
> Are there some issues if index keys are null.

NULLs don't count against UNIQUE constraints, even if multi-column.
See end of this section:


http://www.postgresql.org/docs/7.3/interactive/ddl-
constraints.html#AEN1835

As far as I know, there is no simple way to override this - that is, no
constraint allowing at most one NULL in a column. Same for your
four-column constraint, I believe. You could get what you want by
using another NULL-like value, e.g., an empty string ''. You might be
able to use a trigger, too. - I have little experience with those.

- John Burger
MITRE

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2005-04-11 03:58:25 Re: monitoring database activity on solaris
Previous Message Michael Fuhr 2005-04-11 02:06:04 Re: [GENERAL] Problems with Set Returning Functions (SRFs)