v3-0001-Add-MIN-MAX-aggregate-support-for-uuid.patch

From 01466807cb9186947795a79759de3ccf173f7fbe Mon Sep 17 00:00:00 2001
From: Tristan Partin <tristan@partin.io>
Date: Tue, 23 Jun 2026 17:41:19 +0000
Subject: [PATCH v3] Add MIN/MAX aggregate support for uuid

We already supported various comparison functions in order to implement
various arithmetic operators. A natural next step is adding support for
the MIN and MAX aggregate functions.

Signed-off-by: Tristan Partin <tristan@partin.io>
---
 doc/src/sgml/datatype.sgml            |  4 ++++
 doc/src/sgml/func/func-aggregate.sgml |  6 ++++--
 src/backend/utils/adt/uuid.c          | 18 ++++++++++++++++++
 src/include/catalog/pg_aggregate.dat  |  6 ++++++
 src/include/catalog/pg_proc.dat       | 12 ++++++++++++
 src/test/regress/expected/uuid.out    |  7 +++++++
 src/test/regress/sql/uuid.sql         |  3 +++
 7 files changed, 54 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index d8d91678e8..efbfb471e6 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4413,6 +4413,10 @@ SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' );
     using the UUIDv4 and UUIDv7 algorithms.  Alternatively, UUID values can be generated
     outside of the database using any algorithm.  The data type <type>uuid</type> can be used
     to store any UUID, regardless of the origin and the UUID version.
+    UUIDs are compared lexicographically on their 128-bit value.  For UUIDv7 values,
+    which embed a Unix timestamp in the most significant bits, this ordering corresponds
+    to chronological order, making them suitable for use with aggregate functions such as
+    <function>min</function> and <function>max</function>.
    </para>
 
    <para>
diff --git a/doc/src/sgml/func/func-aggregate.sgml b/doc/src/sgml/func/func-aggregate.sgml
index 8b5eaeb2e9..6a6a4d7e32 100644
--- a/doc/src/sgml/func/func-aggregate.sgml
+++ b/doc/src/sgml/func/func-aggregate.sgml
@@ -509,7 +509,8 @@
         values.  Available for any numeric, string, date/time, or enum type,
         as well as <type>bytea</type>, <type>inet</type>, <type>interval</type>,
         <type>money</type>, <type>oid</type>, <type>oid8</type>,
-        <type>pg_lsn</type>, <type>tid</type>, <type>xid8</type>,
+        <type>pg_lsn</type>, <type>tid</type>, <type>uuid</type>,
+        <type>xid8</type>,
         and also arrays and composite types containing sortable data types.
        </para></entry>
        <entry>Yes</entry>
@@ -528,7 +529,8 @@
         values.  Available for any numeric, string, date/time, or enum type,
         as well as <type>bytea</type>, <type>inet</type>, <type>interval</type>,
         <type>money</type>, <type>oid</type>, <type>oid8</type>,
-        <type>pg_lsn</type>, <type>tid</type>, <type>xid8</type>,
+        <type>pg_lsn</type>, <type>tid</type>, <type>uuid</type>,
+        <type>xid8</type>,
         and also arrays and composite types containing sortable data types.
        </para></entry>
        <entry>Yes</entry>
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 6ee3752ac7..2d33ba2640 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -270,6 +270,24 @@ uuid_cmp(PG_FUNCTION_ARGS)
 	PG_RETURN_INT32(uuid_internal_cmp(arg1, arg2));
 }
 
+Datum
+uuid_larger(PG_FUNCTION_ARGS)
+{
+	pg_uuid_t  *arg1 = PG_GETARG_UUID_P(0);
+	pg_uuid_t  *arg2 = PG_GETARG_UUID_P(1);
+
+	PG_RETURN_UUID_P((uuid_internal_cmp(arg1, arg2) > 0) ? arg1 : arg2);
+}
+
+Datum
+uuid_smaller(PG_FUNCTION_ARGS)
+{
+	pg_uuid_t  *arg1 = PG_GETARG_UUID_P(0);
+	pg_uuid_t  *arg2 = PG_GETARG_UUID_P(1);
+
+	PG_RETURN_UUID_P((uuid_internal_cmp(arg1, arg2) < 0) ? arg1 : arg2);
+}
+
 /*
  * Sort support strategy routine
  */
diff --git a/src/include/catalog/pg_aggregate.dat b/src/include/catalog/pg_aggregate.dat
index 5fd38fd4f2..7bce36ac9c 100644
--- a/src/include/catalog/pg_aggregate.dat
+++ b/src/include/catalog/pg_aggregate.dat
@@ -167,6 +167,9 @@
 { aggfnoid => 'max(bytea)', aggtransfn => 'bytea_larger',
   aggcombinefn => 'bytea_larger', aggsortop => '>(bytea,bytea)',
   aggtranstype => 'bytea' },
+{ aggfnoid => 'max(uuid)', aggtransfn => 'uuid_larger',
+  aggcombinefn => 'uuid_larger', aggsortop => '>(uuid,uuid)',
+  aggtranstype => 'uuid' },
 
 # min
 { aggfnoid => 'min(int8)', aggtransfn => 'int8smaller',
@@ -244,6 +247,9 @@
 { aggfnoid => 'min(bytea)', aggtransfn => 'bytea_smaller',
   aggcombinefn => 'bytea_smaller', aggsortop => '<(bytea,bytea)',
   aggtranstype => 'bytea' },
+{ aggfnoid => 'min(uuid)', aggtransfn => 'uuid_smaller',
+  aggcombinefn => 'uuid_smaller', aggsortop => '<(uuid,uuid)',
+  aggtranstype => 'uuid' },
 
 # count
 { aggfnoid => 'count(any)', aggtransfn => 'int8inc_any',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fa76c7923f..eecb1765cb 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7109,6 +7109,9 @@
 { oid => '6395', descr => 'maximum value of all bytea input values',
   proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'bytea',
   proargtypes => 'bytea', prosrc => 'aggregate_dummy' },
+{ oid => '6517', descr => 'maximum value of all UUID input values',
+  proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'uuid',
+  proargtypes => 'uuid', prosrc => 'aggregate_dummy' },
 
 { oid => '2131', descr => 'minimum value of all bigint input values',
   proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'int8',
@@ -7185,6 +7188,9 @@
 { oid => '6396', descr => 'minimum value of all bytea input values',
   proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'bytea',
   proargtypes => 'bytea', prosrc => 'aggregate_dummy' },
+{ oid => '6518', descr => 'minimum value of all UUID input values',
+  proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'uuid',
+  proargtypes => 'uuid', prosrc => 'aggregate_dummy' },
 
 # count has two forms: count(any) and count(*)
 { oid => '2147',
@@ -9618,6 +9624,12 @@
 { oid => '2960', descr => 'less-equal-greater',
   proname => 'uuid_cmp', proleakproof => 't', prorettype => 'int4',
   proargtypes => 'uuid uuid', prosrc => 'uuid_cmp' },
+{ oid => '6519', descr => 'larger of the two', proname => 'uuid_larger',
+  proleakproof => 't', prorettype => 'uuid', proargtypes => 'uuid uuid',
+  prosrc => 'uuid_larger' },
+{ oid => '6520', descr => 'smaller of the two', proname => 'uuid_smaller',
+  proleakproof => 't', prorettype => 'uuid', proargtypes => 'uuid uuid',
+  prosrc => 'uuid_smaller' },
 { oid => '3300', descr => 'sort support',
   proname => 'uuid_sortsupport', prorettype => 'void',
   proargtypes => 'internal', prosrc => 'uuid_sortsupport' },
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 9c5dda9e9a..cd7cd8b711 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -130,6 +130,13 @@ SELECT COUNT(*) FROM guid1 WHERE guid_field >= '22222222-2222-2222-2222-22222222
      2
 (1 row)
 
+-- min() and max() aggregate test
+SELECT MIN(guid_field), MAX(guid_field) FROM guid1;
+                 min                  |                 max                  
+--------------------------------------+--------------------------------------
+ 11111111-1111-1111-1111-111111111111 | 3f3e3c3b-3a30-3938-3736-353433a2313e
+(1 row)
+
 -- btree and hash index creation test
 CREATE INDEX guid1_btree ON guid1 USING BTREE (guid_field);
 CREATE INDEX guid1_hash  ON guid1 USING HASH  (guid_field);
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index 8cc2ad4061..d0481a1502 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -63,6 +63,9 @@ SELECT COUNT(*) FROM guid1 WHERE guid_field > '22222222-2222-2222-2222-222222222
 -- >= operator test
 SELECT COUNT(*) FROM guid1 WHERE guid_field >= '22222222-2222-2222-2222-222222222222';
 
+-- min() and max() aggregate test
+SELECT MIN(guid_field), MAX(guid_field) FROM guid1;
+
 -- btree and hash index creation test
 CREATE INDEX guid1_btree ON guid1 USING BTREE (guid_field);
 CREATE INDEX guid1_hash  ON guid1 USING HASH  (guid_field);
-- 
Tristan Partin
https://tristan.partin.io