agora inbox for pgsql-hackers@postgresql.org  

help / color / mirror / Atom feed

From: Álvaro Herrera <alvherre@kurilemu.de>
To: Alberto Piai <alberto.piai@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Adding a stored generated column without long-lived locks
Date: Tue, 22 Sep 2026 17:17:05 +0200
Message-ID: <arKaxe9vLCCi_eio@alvherre.pgsql> (raw)
In-Reply-To: <DLLBPPI9MKV4.1EYFI9ZD90Z1E@gmail.com>

On 2026-Sep-21, Alberto Piai wrote:

> Repro:
> 
>   create table tgen.t_repro_1 (a numeric, b numeric);
>   insert into tgen.t_repro_1 values ('1.0', '1.00'), ('1.0', '1.0');
>   create unique index on tgen.t_repro_1 ((b::text));
>   alter table tgen.t_repro_1
>     add constraint chk_gen check (b is not distinct from a);
> 
>   alter table tgen.t_repro_1
>     alter b add generated using constraint chk_gen stored;
> 
>   update tgen.t_repro_1 set a = a;
>   ERROR:  duplicate key value violates unique constraint "t_repro_1_b_idx"
>   DETAIL:  Key ((b::text))=(1.0) already exists.

Does this _matter_?

Does anybody want to have a generated numeric column that's identical to
the base column except it has more zeroes in the decimal part?  Or to
generate a text column that's not binary identical to another text
column but compares equal when viewed through an nondeterministic
collation?

I think the answer is no.

I'm pretty okay with saying that if you want to add a new column that's
generated in such a way, then you have to go the normal route of using
the blocking command.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"La experiencia nos dice que el hombre peló millones de veces las patatas,
pero era forzoso admitir la posibilidad de que en un caso entre millones,
las patatas pelarían al hombre" (Ijon Tichy)

view thread (36+ messages)  latest in thread

Message-ID: <arKaxe9vLCCi_eio@alvherre.pgsql>
Permalink:  ../arKaxe9vLCCi_eio@alvherre.pgsql/
Also on:    postgresql.org/message-id/arKaxe9vLCCi_eio@alvherre.pgsql

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-hackers@postgresql.org
  Cc: alvherre@kurilemu.de, alberto.piai@gmail.com, laurenz.albe@cybertec.at
  Subject: Re: Adding a stored generated column without long-lived locks
  In-Reply-To: <arKaxe9vLCCi_eio@alvherre.pgsql>

* 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