Re: unique index with several columns

On 03/04/22 21:32, Tom Lane wrote:
> Alexey M Boltenkov <padrebolt(at)yandex(dot)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)