From: | Klaus Kaisersberger <klaus(dot)kaisersberger(at)outlook(dot)com> |
---|---|
To: | "woodconsult(at)outlook(dot)de" <woodconsult(at)outlook(dot)de>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Alexey M Boltenkov <padrebolt(at)yandex(dot)ru> |
Cc: | "Voillequin, Jean-Marc" <Jean-Marc(dot)Voillequin(at)moodys(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-sql(at)lists(dot)postgresql(dot)org" <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | RE: unique index with several columns |
Date: | 2022-03-04 19:18:07 |
Message-ID: | PAXP193MB2058717C6EB0DE4B0A6B290FE3059@PAXP193MB2058.EURP193.PROD.OUTLOOK.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 separate function.
-----Original Message-----
From: Klaus Kaisersberger <woodconsult(at)outlook(dot)de>
Sent: Friday, March 4, 2022 7:57 PM
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; Alexey M Boltenkov <padrebolt(at)yandex(dot)ru>
Cc: Voillequin, Jean-Marc <Jean-Marc(dot)Voillequin(at)moodys(dot)com>; David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>; pgsql-sql(at)lists(dot)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Friday, March 4, 2022 7:32 PM
To: Alexey M Boltenkov <padrebolt(at)yandex(dot)ru>
Cc: Voillequin, Jean-Marc <Jean-Marc(dot)Voillequin(at)moodys(dot)com>; David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>; pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Re: unique index with several columns
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
From | Date | Subject | |
---|---|---|---|
Next Message | Klaus Kaisersberger | 2022-03-04 20:02:07 | RE: unique index with several columns |
Previous Message | David G. Johnston | 2022-03-04 19:06:04 | Re: unique index with several columns |