Unique index with Null value in one field

From: Hrishi Joshi <hjoshi(at)abcsinc(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unique index with Null value in one field
Date: 2005-10-11 22:36:59
Message-ID: 1129070219.6672.11.camel@javadev3
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2005-10-11 22:52:16 Re: [GENERAL] Oracle buys Innobase
Previous Message Bruce Momjian 2005-10-11 22:36:35 Re: Oracle buys Innobase