Re: Unique index with Null value in one field

Hrishi Joshi <hjoshi@abcsinc.com> writes:
> 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.

That is the behavior defined by the SQL standard.

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

You can't.  Rethink your data representation, instead.  You are misusing
NULL if you think that it represents something unique.

            regards, tom lane