| 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: | Whole Thread | Raw Message | 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 |