On Wed, 12 Aug 2026 at 18:36, Matthias van de Meent <[email protected]> wrote: > > In the attached patch.
Rebased again on master, to fix bitrot from 483e918674. Kind regards, Matthias van de Meent Databricks (https://www.databricks.com)
From 120b640ccbf67701f4c62c14e35727d742c357c2 Mon Sep 17 00:00:00 2001 From: Matthias van de Meent <[email protected]> Date: Wed, 10 Dec 2025 18:45:19 +0100 Subject: [PATCH v5] Add SQL-level datum equality tests This enables improved performance for users that need to test for exact bytewise differences in SQL; e.g. to see if an UPDATE is really necessary with a set of externally provided values. A workaround around this limitation was often possible, but no generic method was available that was this performant, nor as accessible to all types. --- doc/src/sgml/func/func-comparison.sgml | 25 ++++++ src/backend/utils/adt/pseudotypes.c | 51 ++++++++++++ src/include/catalog/pg_proc.dat | 7 ++ src/test/regress/expected/misc_functions.out | 81 ++++++++++++++++++++ src/test/regress/expected/opr_sanity.out | 1 + src/test/regress/sql/misc_functions.sql | 25 ++++++ 6 files changed, 190 insertions(+) diff --git a/doc/src/sgml/func/func-comparison.sgml b/doc/src/sgml/func/func-comparison.sgml index ecb1d89463a..df806d992c3 100644 --- a/doc/src/sgml/func/func-comparison.sgml +++ b/doc/src/sgml/func/func-comparison.sgml @@ -653,6 +653,31 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in <returnvalue>1</returnvalue> </para></entry> </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_datum_image_equal</primary> + </indexterm> + <function>pg_datum_image_equal</function> ( <type>anyelement</type>, <type>anyelement</type> ) + <returnvalue>boolean</returnvalue> + </para> + <para> + Returns whether the values have the same exact binary representation. + </para> + <para> + <literal>pg_datum_image_equal('1.0'::numeric, '1.0'::numeric)</literal> + <returnvalue>true</returnvalue> + </para> + <para> + <literal>pg_datum_image_equal('1.0'::numeric, '1.00'::numeric)</literal> + <returnvalue>false</returnvalue> + </para> + <para> + <literal>pg_datum_image_equal(NULL::numeric, NULL::numeric)</literal> + <returnvalue>true</returnvalue> + </para></entry> + </row> </tbody> </tgroup> </table> diff --git a/src/backend/utils/adt/pseudotypes.c b/src/backend/utils/adt/pseudotypes.c index 4581c4b1697..93a67e53026 100644 --- a/src/backend/utils/adt/pseudotypes.c +++ b/src/backend/utils/adt/pseudotypes.c @@ -23,7 +23,9 @@ #include "postgres.h" #include "libpq/pqformat.h" +#include "utils/datum.h" #include "utils/fmgrprotos.h" +#include "utils/lsyscache.h" /* @@ -375,3 +377,52 @@ PSEUDOTYPE_DUMMY_IO_FUNCS(anyelement); PSEUDOTYPE_DUMMY_IO_FUNCS(anynonarray); PSEUDOTYPE_DUMMY_IO_FUNCS(anycompatible); PSEUDOTYPE_DUMMY_IO_FUNCS(anycompatiblenonarray); + +/* + * Compares two datums of the same (any) type, and returns whether they have + * the same binary representation. + */ +Datum +pg_datum_image_equal(PG_FUNCTION_ARGS) +{ + bool eq; + + if (PG_ARGISNULL(0) != PG_ARGISNULL(1)) + { + eq = false; + } + else if (PG_ARGISNULL(0)) + { + /* both NULL */ + eq = true; + } + else + { + Oid typ; + Datum arg0; + Datum arg1; + bool typbyval; + char typalign; + int16 typlen; + + typ = get_fn_expr_argtype(fcinfo->flinfo, 0); + + if (!OidIsValid(typ)) + { + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("could not determine type"))); + } + + Assert(typ == get_fn_expr_argtype(fcinfo->flinfo, 1)); + + arg0 = PG_GETARG_DATUM(0); + arg1 = PG_GETARG_DATUM(1); + + get_typlenbyvalalign(typ, &typlen, &typbyval, &typalign); + + eq = datum_image_eq(arg0, arg1, typbyval, typlen); + } + + PG_RETURN_BOOL(eq); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 6979c7d1161..eb31d56c53c 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12776,4 +12776,11 @@ proname => 'hashoid8extended', prorettype => 'int8', proargtypes => 'oid8 int8', prosrc => 'hashoid8extended' }, +{ oid => '9200', + descr => 'test if two values have the same binary representation', + proname => 'pg_datum_image_equal', proisstrict => 'f', + proleakproof => 't', prorettype => 'bool', + proargtypes => 'anyelement anyelement', + prosrc => 'pg_datum_image_equal' }, + ] diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out index 2990e0c4f28..a1cc5f0c1bb 100644 --- a/src/test/regress/expected/misc_functions.out +++ b/src/test/regress/expected/misc_functions.out @@ -901,3 +901,84 @@ SELECT test_pg_locale_apis(to_regcollation('en-x-icu')); (1 row) +-- test pg_datum_image_equal(..., ...) +WITH values AS ( + SELECT val::numeric FROM ( + VALUES ('1.0'), + ('1.00'), + ('2.0'), + ('2.1'), + (NULL) + ) AS v(val) +) +SELECT a.val a, b.val b, a.val = b.val eq, pg_datum_image_equal(a.val, b.val) pdie +FROM values a CROSS JOIN values b; + a | b | eq | pdie +------+------+----+------ + 1.0 | 1.0 | t | t + 1.0 | 1.00 | t | f + 1.0 | 2.0 | f | f + 1.0 | 2.1 | f | f + 1.0 | | | f + 1.00 | 1.0 | t | f + 1.00 | 1.00 | t | t + 1.00 | 2.0 | f | f + 1.00 | 2.1 | f | f + 1.00 | | | f + 2.0 | 1.0 | f | f + 2.0 | 1.00 | f | f + 2.0 | 2.0 | t | t + 2.0 | 2.1 | f | f + 2.0 | | | f + 2.1 | 1.0 | f | f + 2.1 | 1.00 | f | f + 2.1 | 2.0 | f | f + 2.1 | 2.1 | t | t + 2.1 | | | f + | 1.0 | | f + | 1.00 | | f + | 2.0 | | f + | 2.1 | | f + | | | t +(25 rows) + +WITH values AS ( + SELECT val::jsonb FROM ( + VALUES ('{"key": 1.0}'), + ('{"key": 1.00}'), + ('{"key": 2.0}'), + ('{"key": null}'), + (NULL) + ) AS v(val) +) +SELECT a.val a, b.val b, a.val = b.val eq, pg_datum_image_equal(a.val, b.val) pdie +FROM values a CROSS JOIN values b; + a | b | eq | pdie +---------------+---------------+----+------ + {"key": 1.0} | {"key": 1.0} | t | t + {"key": 1.0} | {"key": 1.00} | t | f + {"key": 1.0} | {"key": 2.0} | f | f + {"key": 1.0} | {"key": null} | f | f + {"key": 1.0} | | | f + {"key": 1.00} | {"key": 1.0} | t | f + {"key": 1.00} | {"key": 1.00} | t | t + {"key": 1.00} | {"key": 2.0} | f | f + {"key": 1.00} | {"key": null} | f | f + {"key": 1.00} | | | f + {"key": 2.0} | {"key": 1.0} | f | f + {"key": 2.0} | {"key": 1.00} | f | f + {"key": 2.0} | {"key": 2.0} | t | t + {"key": 2.0} | {"key": null} | f | f + {"key": 2.0} | | | f + {"key": null} | {"key": 1.0} | f | f + {"key": null} | {"key": 1.00} | f | f + {"key": null} | {"key": 2.0} | f | f + {"key": null} | {"key": null} | t | t + {"key": null} | | | f + | {"key": 1.0} | | f + | {"key": 1.00} | | f + | {"key": 2.0} | | f + | {"key": null} | | f + | | | t +(25 rows) + diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 67cae397861..3bfce684042 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -892,6 +892,7 @@ tid_block(tid) tid_offset(tid) uuid_larger(uuid,uuid) uuid_smaller(uuid,uuid) +pg_datum_image_equal(anyelement,anyelement) -- Check that functions without argument are not marked as leakproof. SELECT p1.oid::regprocedure FROM pg_proc p1 JOIN pg_namespace pn diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql index 950d9ab1a4a..7acf0d856b9 100644 --- a/src/test/regress/sql/misc_functions.sql +++ b/src/test/regress/sql/misc_functions.sql @@ -381,3 +381,28 @@ SELECT test_pg_locale_apis(to_regcollation('pg_c_utf8')); -- en-x-icu is present when ICU collations were imported at initdb. SELECT test_pg_locale_apis(to_regcollation('en-x-icu')); + +-- test pg_datum_image_equal(..., ...) +WITH values AS ( + SELECT val::numeric FROM ( + VALUES ('1.0'), + ('1.00'), + ('2.0'), + ('2.1'), + (NULL) + ) AS v(val) +) +SELECT a.val a, b.val b, a.val = b.val eq, pg_datum_image_equal(a.val, b.val) pdie +FROM values a CROSS JOIN values b; + +WITH values AS ( + SELECT val::jsonb FROM ( + VALUES ('{"key": 1.0}'), + ('{"key": 1.00}'), + ('{"key": 2.0}'), + ('{"key": null}'), + (NULL) + ) AS v(val) +) +SELECT a.val a, b.val b, a.val = b.val eq, pg_datum_image_equal(a.val, b.val) pdie +FROM values a CROSS JOIN values b; -- 2.50.1 (Apple Git-155)
