Re: unique constraint with significant nulls?

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  - 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