Re: unique constraint with significant nulls?

[Mike Blackwell <mike.blackwell@xxxxxxx>]

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

On Tue, Sep 25, 2012 at 10:32 AM, hubert depesz lubaczewski <depesz@xxxxxxxxxx> 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/