Re: Unique index with Null value in one field

On 11 Oct 2005 17:36:59 -0500, Hrishi Joshi <hjoshi(at)abcsinc(dot)com> wrote:
> 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.
>
>

maybe with a function and comparing yourself...

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)