Re: uniqueness constraint with NULLs

A. Kretschmer wrote:
> In response to Robert Edwards :
>> Can anyone suggest a way that I can impose uniqueness on a and b when
>> c is NULL?
> 
> Sure, use a functional index:
> 
> test=# create table bobtest (a int, b int, c int);
> CREATE TABLE
> test=*# create unique index idx_bobtest on
> bobtest(a,b,coalesce(c::text,'NULL'));
> CREATE INDEX
> test=*# insert into bobtest (a, b) values (1, 4);
> INSERT 0 1
> test=*# insert into bobtest (a, b, c) values (1, 4, NULL);
> ERROR:  duplicate key value violates unique constraint "idx_bobtest"
> test=!#
> 
> 
> Regards, Andreas

Beautiful!

Many thanks,

Bob Edwards.