Re: Unique index with Null value in one field

On 11 Oct 2005 17:36:59 -0500, Hrishi Joshi <hjoshi@abcsinc.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 ;)