Re: unique index with several columns

You need the new v15 feature:

NULLS [NOT] DISTINCT
Specifies whether for a unique index, null values should be considered
distinct (not equal). The default is that they are distinct, so that a
unique index could contain multiple null values in a column.

https://www.postgresql.org/docs/devel/sql-createindex.html

On 03/04/22 19:11, Voillequin, Jean-Marc wrote:
>
> Yes, it helps, thank you.
>
> But I have many tables with many indexes and many columns!
>
> Tons of combinations… ☹
>
> *From:* David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> *Sent:* Friday, March 4, 2022 4:32 PM
> *To:* Voillequin, Jean-Marc <Jean-Marc(dot)Voillequin(at)moodys(dot)com>
> *Cc:* pgsql-sql(at)lists(dot)postgresql(dot)org
> *Subject:* Re: unique index with several columns
>
> *CAUTION:*This email originated from outside of Moody's. Do not click
> links or open attachments unless you recognize the sender and know the
> content is safe.
>
> On Fri, Mar 4, 2022 at 8:07 AM Voillequin, Jean-Marc
> <Jean-Marc(dot)Voillequin(at)moodys(dot)com
> <mailto: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.
>
> -----------------------------------------
> Moody's monitors email communications through its networks for
> regulatory compliance purposes and to protect its customers, employees
> and business and where allowed to do so by applicable law. The
> information contained in this e-mail message, and any attachment
> thereto, is confidential and may not be disclosed without our express
> permission. If you are not the intended recipient or an employee or
> agent responsible for delivering this message to the intended
> recipient, you are hereby notified that you have received this message
> in error and that any review, dissemination, distribution or copying
> of this message, or any attachment thereto, in whole or in part, is
> strictly prohibited. If you have received this message in error,
> please immediately notify us by telephone, fax or e-mail and delete
> the message and all of its attachments. Every effort is made to keep
> our network free from viruses. You should, however, review this e-mail
> message, as well as any attachment thereto, for viruses. We take no
> responsibility and have no liability for any computer virus which may
> be transferred via this e-mail message.
> -----------------------------------------