From: Alexey M Boltenkov <padrebolt@yandex.ru>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
Cc: 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 23:47:12 +0300
Message-ID: <8b4b7a41-5181-60db-2bd9-a6fabcd6226f@yandex.ru> (raw)
In-Reply-To: <3966274.1646418725@sss.pgh.pa.us>
References: <MN2PR20MB2735507A85A89B7279AB6A2CBE059@MN2PR20MB2735.namprd20.prod.outlook.com>
<CAKFQuwa4dELLaEdm2UsZK-LyRA08VYOYQZyxNs8zF25nYzgv3w@mail.gmail.com>
<MN2PR20MB2735EB2F5DA362B53277BE11BE059@MN2PR20MB2735.namprd20.prod.outlook.com>
<6699c0e9-e358-1fc2-7ce4-7a24d4bc419e@yandex.ru>
<3966274.1646418725@sss.pgh.pa.us>
On 03/04/22 21:32, Tom Lane wrote:
> Alexey M Boltenkov <padrebolt@yandex.ru> writes:
>> You need the new v15 feature:
>> NULLS [NOT] DISTINCT
> That won't replicate the behavior shown by the OP though.
> In particular, not the weird inconsistency for all-null rows.
>
> regards, tom lane
>
But why?
# create table t(c1 char, c2 char);
CREATE TABLE
# create unique index idx on t(c1,c2) nulls not distinct where c1 is not
null or c2 is not null;
CREATE INDEX
# insert into t(c1,c2) values (null,null);
INSERT 0 1
# insert into t(c1,c2) values (null,null);
INSERT 0 1
# insert into t(c1,c2) values ('a',null);
INSERT 0 1
# insert into t(c1,c2) values ('a',null);
ERROR: 23505: duplicate key value violates unique constraint "idx"
DETAIL: Key (c1, c2)=(a, null) already exists.
SCHEMA NAME: public
TABLE NAME: t
CONSTRAINT NAME: idx
LOCATION: _bt_check_unique, nbtinsert.c:664
# \d+ t
Table "public.t"
Column │ Type │ Collation │ Nullable │ Default │ Storage │
Compression │ Stats target │ Description
════════╪══════════════╪═══════════╪══════════╪═════════╪══════════╪═════════════╪══════════════╪═════════════
c1 │ character(1) │ │ │ │ extended
│ │ │
c2 │ character(1) │ │ │ │ extended
│ │ │
Indexes:
"idx" UNIQUE, btree (c1, c2) NULLS NOT DISTINCT WHERE c1 IS NOT
NULL OR c2 IS NOT NULL
Access method: heap
# table t;
c1 │ c2
════╪════
¤ │ ¤
¤ │ ¤
a │ ¤
(3 rows)
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, tgl@sss.pgh.pa.us, 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: <8b4b7a41-5181-60db-2bd9-a6fabcd6226f@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox