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.