Sorry, mixed the lines up:
create unique index idx on t ((
case
when c1 is null and c2 is null then null
when c1 is null and c2 is not null then ARRAY[null,c2]
when c1 is not null and c2 is null then ARRAY[c1,null]
else ARRAY[c1,c2]
end
));
This could be generically extended to an arbitrary number of columns by counting the number of null column values in a
separatefunction.
-----Original Message-----
From: Klaus Kaisersberger <woodconsult@outlook.de>
Sent: Friday, March 4, 2022 7:57 PM
To: Tom Lane <tgl@sss.pgh.pa.us>; Alexey M Boltenkov <padrebolt@yandex.ru>
Cc: Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>; David G. Johnston <david.g.johnston@gmail.com>;
pgsql-sql@lists.postgresql.org
Subject: RE: unique index with several columns
This should solve the task:
create unique index idx on t ((
case
when c1 is null and c2 is null then null
when c1 is null and c2 is not null then '{c2}'
when c1 is not null and c2 is null then '{c1}'
else '{c1,c2}'
end));
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, March 4, 2022 7:32 PM
To: Alexey M Boltenkov <padrebolt@yandex.ru>
Cc: Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>; David G. Johnston <david.g.johnston@gmail.com>;
pgsql-sql@lists.postgresql.org
Subject: Re: unique index with several columns
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
