agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Alexey M Boltenkov <padrebolt@yandex.ru>
To: Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: unique index with several columns
Date: Fri, 4 Mar 2022 21:26:03 +0300
Message-ID: <6699c0e9-e358-1fc2-7ce4-7a24d4bc419e@yandex.ru> (raw)
In-Reply-To: <MN2PR20MB2735EB2F5DA362B53277BE11BE059@MN2PR20MB2735.namprd20.prod.outlook.com>
References: <MN2PR20MB2735507A85A89B7279AB6A2CBE059@MN2PR20MB2735.namprd20.prod.outlook.com>
<CAKFQuwa4dELLaEdm2UsZK-LyRA08VYOYQZyxNs8zF25nYzgv3w@mail.gmail.com>
<MN2PR20MB2735EB2F5DA362B53277BE11BE059@MN2PR20MB2735.namprd20.prod.outlook.com>
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.g.johnston@gmail.com>
> *Sent:* Friday, March 4, 2022 4:32 PM
> *To:* Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
> *Cc:* pgsql-sql@lists.postgresql.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.Voillequin@moodys.com
> <mailto:Jean-Marc.Voillequin@moodys.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.
> -----------------------------------------
view thread (12+ messages) latest in thread
Message-ID: <6699c0e9-e358-1fc2-7ce4-7a24d4bc419e@yandex.ru>
Permalink: ../6699c0e9-e358-1fc2-7ce4-7a24d4bc419e@yandex.ru/
Also on: postgresql.org/message-id/6699c0e9-e358-1fc2-7ce4-7a24d4bc419e@yandex.ru
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: padrebolt@yandex.ru, Jean-Marc.Voillequin@moodys.com, david.g.johnston@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: unique index with several columns
In-Reply-To: <6699c0e9-e358-1fc2-7ce4-7a24d4bc419e@yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox