Re: Unique index with Null value in one field

On Tue, 11 Oct 2005, Hrishi Joshi wrote:

> I need to define a Unique index on 3 non-PK fields (composite key) on my
> table in PostgreSQL 8.0.3.
>
> The problem is, if any of those 3 fields is Null, PostgreSQL allows
> duplicate rows to be inserted. While searching through archives, I found
> more information about this.
>
> But I need to know how can I make PostgreSQL throw error on attempt to
> insert second record having same 3 field values, one of them being Null.
>
>
> ------------------------------------------------
> myid | field1 | field2 | field3 | description
> PK | <--- Unique Index ---> |
> ------------------------------------------------
> 100 | ABC | XYZ | <null> | Record 1 -> This is ok.
> 101 | ABC | XYZ | <null> | Record 2 -> * This should error!
> ------------------------------------------------
>
> Fields {field1, field2, field3} have unique index on them and "myid" is
> the primary key of my table.
>
>
> Oracle 9i throws exception in such case, but PostgreSQL does not.

We're pretty sure that the standard UNIQUE constraint requires this
behavior and our unique index is the mechanism for checking that
constraint and so has the same behavior.

If there's a non-null value that you know can't be there, you might be
able to use a unique index on something like
(coalesce(field1, <v>)), (coalesce(field2,<v>)), (coalesce(field3,<v>))

Otherwise, you might be able to use a set of partial unique indexes for
the null cases. I'm not sure how many it would require, though.