Unique index with Null value in one field - Mailing list pgsql-general

Hi,

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.


Thanks,
- Hrishi Joshi.