Feedback-ID: ie3de48e3:Fastmail
Date: Tue, 22 Sep 2026 17:17:05 +0200
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
To: Alberto Piai <alberto.piai@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-hackers@postgresql.org
Subject: Re: Adding a stored generated column without long-lived locks
Message-ID: <arKaxe9vLCCi_eio@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <DLLBPPI9MKV4.1EYFI9ZD90Z1E@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/arKaxe9vLCCi_eio%40alvherre.pgsql>
Precedence: bulk

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)