public inbox for [email protected]
help / color / mirror / Atom feedFrom: Matthias van de Meent <[email protected]>
Cc: jian he <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: SQL-level pg_datum_image_equal
Date: Wed, 25 Mar 2026 15:46:08 +0100
Message-ID: <CAEze2WjUyYhcUwzaPiQbe-xBB_knbnG8Xr_9ACL7HrGVx=Vydw@mail.gmail.com> (raw)
In-Reply-To: <CAEze2Wg4Fj+9LQOv=J5cqwapD8vz3oDh1B0iyX7RwesvseH9gg@mail.gmail.com>
References: <CAEze2WhsqYjg0oGY+7yooimUK7zRc9PY9u8u-Oo=VmJ+DAAkKg@mail.gmail.com>
<CACJufxFV5KqmF86upFmX2tPzfKMWjWNf8+uYiYufLKatOsYXQw@mail.gmail.com>
<CAEze2Wg4Fj+9LQOv=J5cqwapD8vz3oDh1B0iyX7RwesvseH9gg@mail.gmail.com>
On Mon, 22 Dec 2025 at 16:25, Matthias van de Meent
<[email protected]> wrote:
>
> On Sat, 20 Dec 2025 at 14:15, jian he <[email protected]> wrote:
> > maybe Table 9.76
> > (https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-CATALOG)
> > is the right place for this function.
>
> I think table 9.3
> (https://www.postgresql.org/docs/18/functions-comparison.html#FUNCTIONS-COMPARISON-FUNC-TABLE)
> makes more sense, as this is more a compare function than one that
> exposes catalog information about the input.
Attached is v2, which adds the new function to the docs, in addition
to rebasing the patch onto master.
Kind regards,
Matthias van de Meent
Databricks (https://www.databricks.com)
Attachments:
[application/octet-stream] v2-0001-Add-SQL-level-datum-equality-tests.patch (4.6K, 2-v2-0001-Add-SQL-level-datum-equality-tests.patch)
download | inline diff:
From febf8d64154bdd40997398fb0ff79c27b726fc72 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 v2] 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, or
worked for all types.
---
doc/src/sgml/func/func-comparison.sgml | 21 ++++++++++
src/backend/utils/adt/pseudotypes.c | 51 ++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 7 ++++
src/test/regress/expected/opr_sanity.out | 1 +
4 files changed, 80 insertions(+)
diff --git a/doc/src/sgml/func/func-comparison.sgml b/doc/src/sgml/func/func-comparison.sgml
index ecb1d89463a..2f970fecda3 100644
--- a/doc/src/sgml/func/func-comparison.sgml
+++ b/doc/src/sgml/func/func-comparison.sgml
@@ -653,6 +653,27 @@ 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></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 0118e970dda..ea59ede9660 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12851,4 +12851,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/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 6ff4d7ee901..df25551c48d 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -887,6 +887,7 @@ oid8le(oid8,oid8)
oid8gt(oid8,oid8)
oid8ge(oid8,oid8)
btoid8cmp(oid8,oid8)
+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
--
2.50.1 (Apple Git-155)
view thread (11+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: SQL-level pg_datum_image_equal
In-Reply-To: <CAEze2WjUyYhcUwzaPiQbe-xBB_knbnG8Xr_9ACL7HrGVx=Vydw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox