On Tue, 22 Sept 2026 at 15:02, Laurenz Albe <[email protected]> wrote: > > On Mon, 2026-09-21 at 23:48 +0200, Alberto Piai wrote: > > in an attempt to avoid wasting committer time, I decided to use an > > LLM-based tool to analyze this patch and try to come up with > > counterexamples to break my usage of IS NOT DISTINCT FROM. > > > > It produced an example showing how IS NOT DISTINCT FROM isn't good > > enough either for my purpose. > > > > The problem is types where some values are evaluated as equal (according > > to =), but don't have the same representation. In conjuction with a > > unique index, they could be used to put a database in an invalid state > > where rewriting operations (update ... set a = a or pg_dump/pg_restore) > > fail. > > > > 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. > > > > > > I will have to re-think this quite a bit. > > Ho, hum. Case insensitive collations would be another example. > > There is no way to write "is binary identical to" in SQL, as far as > I can tell.
None yet: [0] is a patch that adds a pg_datum_image_equal(a, b) function that should allow users to detect binary differences in all types of values. It won't detect differences in the way data is stored (it only checks detoasted datum differences, so toast ID or compression types are not tested), but I think the function added in that patch is sufficient for this use case, too. > Perhaps a solution would be to force the use of the data type's send > function in the check constraint: Going through sendfn can be rather expensive (even compared to detoasting), which is why I started the work on [0]: At databricks we had a comparable use case of needing to detect exactly equal input data, as cheap as possible. Note that "is not distinct from" will work correctly when the underlying = operator is part of a btree opfamily whose equalimage() support function returns true; this is something that you can use to make sure the selected constraint in ADD GENERATED can safely be used even if some other types won't work. The equalimage() support function's result indicates whether the type has a strict total ordering (when true), or whether there are values which have different representations but are considered equal in this opclass' sort ordering (when false). This same support function also indicates whether deduplication can be used for the indexed data (when true) or not (when false). Kind regards, Matthias van de Meent Databricks (https://www.databricks.com) [0] https://commitfest.postgresql.org/patch/6309/
