Re: unique constraint with significant nulls?

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(dot)Blackwell(at)rrd(dot)com
http://www.rrdonnelley.com

<http://www.rrdonnelley.com/>
* <Mike(dot)Blackwell(at)rrd(dot)com>*

On Tue, Sep 25, 2012 at 12:37 PM, Andreas Joseph Krogh <andreak(at)officenet(dot)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(at)officenet(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>