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. We can't start ignoring and tossing significant zeroes in numeric types, because they have meaning in scientific calculations, and have different outputs for various operations, including casts to text, but also functions like scale. Generated columns should (must) respect that, and produce the same result between SELECT (gen_col_expr) and SELECT gen_col. > 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 don't think anyone wants that specifically, but if the database state contains that numeric column that's identical to the first, but with more zeroes, or a text column with different encoding, then that must be detected before we go ahead and fully consider the target column to be a generated version of the source column; the outputs must be identical even if the default = operator thinks they have the same value. Note that this issue extends to at least jsonb, which uses textual and numeric comparators internally for the sorting operations; it's certainly possible to remove or add a few zeroes here and there in projection steps if you're not careful when you populate the not-yet-GENERATED column's data. Kind regards, Matthias van de Meent Databricks (https://www.databricks.com)
