Ah. A pair of constraints. I see. Thanks!
__________________________________________________________________________________ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com <http://www.rrdonnelley.com/> * <mike.blackw...@rrd.com>* On Tue, Sep 25, 2012 at 12:37 PM, Andreas Joseph Krogh <andr...@officenet.no > wrote: > On 09/25/2012 05:05 PM, Mike Blackwell wrote: > >> How would one go about building a multi-column unique constraint where >> null is a significant value, eg. (1, NULL) <> (2, NULL)? >> >> I see a number of references to not being able to use an index for this, >> but no mention of an alternative. Any pointers would be appreciated >> > > create table my_table( > some_column varchar not null, > other_column varchar); > > create unique index my_idx on my_table(some_column, other_column) where > other_column is not null; > create unique index my_fish_idx on my_table(some_column) where > other_column is null; > > insert into my_table (some_column, other_column) values('a', 'a'); > insert into my_table (some_column, other_column) values('a', 'b'); > insert into my_table (some_column) values('a'); > insert into my_table (some_column) values('b'); > > -- fails > insert into my_table (some_column, other_column) values('a', 'a'); > -- also fails > insert into my_table (some_column) values('a'); > > result: > > andreak=# insert into my_table (some_column, other_column) values('a', > 'a'); > ERROR: duplicate key value violates unique constraint "my_idx" > DETAIL: Key (some_column, other_column)=(a, a) already exists. > > andreak=# insert into my_table (some_column) values('a'); > ERROR: duplicate key value violates unique constraint "my_fish_idx" > DETAIL: Key (some_column)=(a) already exists. > > > -- > Andreas Joseph Krogh<andr...@officenet.no> - mob: +47 909 56 963 > Senior Software Developer / CEO - OfficeNet AS - http://www.officenet.no > Public key: > http://home.officenet.no/~**andreak/public_key.asc<http://home.officenet.no/~andreak/public_key.asc> > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general> >
