Re: unique constraint with significant nulls?

On Tue, Sep 25, 2012 at 12:37 PM, Andreas Joseph Krogh <andreak@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<andreak@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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general