Thread: Duplicate records in table

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