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