Re: uniqueness constraint with NULLs - Mailing list pgsql-sql

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
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net