Horton

Thread

  1. unique constraint with significant nulls?

    Mike Blackwell <mike.blackwell@rrd.com> — 2012-09-25T15:05:15Z

    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

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


<http://www.rrdonnelley.com/>
* <Mike.Blackwell@rrd.com>*

  2. Re: unique constraint with significant nulls?

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-09-25T15:32:57Z

    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/

  3. Re: unique constraint with significant nulls?

    Mike Blackwell <mike.blackwell@rrd.com> — 2012-09-25T16:34:36Z

    Interesting, but that assumes there's a value to use in the coalesce that
isn't a valid data value.

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


<http://www.rrdonnelley.com/>
* <Mike.Blackwell@rrd.com>*


On Tue, Sep 25, 2012 at 10:32 AM, hubert depesz lubaczewski <
depesz@depesz.com> wrote:

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

  4. Re: unique constraint with significant nulls?

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-09-25T16:58:05Z

    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/

  5. Re: unique constraint with significant nulls?

    Andreas Joseph Krogh <andreak@officenet.no> — 2012-09-25T17:37:38Z

    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

  6. Re: unique constraint with significant nulls?

    Mike Blackwell <mike.blackwell@rrd.com> — 2012-09-26T14:33:06Z

    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.Blackwell@rrd.com
http://www.rrdonnelley.com


<http://www.rrdonnelley.com/>
* <Mike.Blackwell@rrd.com>*


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