On Wed Sep 23, 2026 at 12:26 PM CEST, Laurenz Albe wrote: > On Wed, 2026-09-23 at 12:07 +0200, Matthias van de Meent wrote: >> On Tue, 22 Sept 2026 at 17:17, Álvaro Herrera <[email protected]> wrote: >> > 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_? >> >> Yes, this does matter. > > I tend to agree.
I too think this matters. The main arugment is IMHO the catastrophic failure mode: successful ALTER TABLE command, broken pg_restore... I would hate to put anyone in that situation. I find Matthias' proposal of exposing a function to check image equality very compelling for the purpose of this patch: besides fixing this problem, it would also make the command usable for data types which don't define = (json), as well as those which don't (can't?) define equalimage()... jsonb, numeric but also tsvector and PostGIS geometry. I'll take some time to study/review the patch and the discussion around it. Kind regards, Alberto -- Alberto Piai Sensational AG Zürich, Switzerland
