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
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) |