Thread: unique constraint with significant nulls?

On Tue, Sep 25, 2012 at 10:05:15AM -0500, 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 unique index zzz on table ((column is null), coalesce(column, 'whatever'));

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

__________________________________________________________________________________
Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com

On Tue, Sep 25, 2012 at 11:34:36AM -0500, Mike Blackwell wrote:
> Interesting, but that assumes there's a value to use in the coalesce that
> isn't a valid data value.

no, it doesn't.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

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