Re: unique constraint with significant nulls?

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