Re: unique index with several columns

On Fri, Mar 4, 2022 at 8:07 AM Voillequin, Jean-Marc <
Jean-Marc(dot)Voillequin(at)moodys(dot)com> wrote:

>
>
> When one of the field is null, PG considers that the tuple is not the same:
>
> ('a',null) is not equal to ('a',null)
>
> So, the unique constraint is not violated in PG.
>
>
>
> But is there a way to have the same feature than Oracle?
>
>
>
> I already tried with:
>
> create unique index idx on t(coalesce(c1,''),coalesce(c2,''))
>
>
>
> But in this case, I cannot insert several (null,null) without raising a
> duplicate key error.
>
>
>
You just said you wanted nulls to be considered equal to each other...

Not tested but these should work in conjunction with each other:

CREATE UNIQUE INDEX idx ON t (c1, c2) WHERE c1 IS NOT NULL AND c2 IS NOT
NULL;
CREATE UNIQUE INDEX idx2 ON t (c1) WHERE c2 IS NULL AND c1 IS NOT NULL;
CREATE UNIQUE INDEX idx3 ON t (c2) WHERE c1 IS NULL AND c2 IS NOT NULL;
--CREATE INDEX idx4 ON t (c1, c2) WHERE c1 IS NULL AND c2 IS NULL;
(pointless...but symmetric with the others)

Hopefully this helps.

David J.