On Wed Jun 24, 2026 at 5:59 AM UTC, Bharath Rupireddy wrote:
> Hi,
>
> On Tue, Jun 23, 2026 at 11:05 AM Tristan Partin <[email protected]> wrote:
>>
>> On Tue Jun 23, 2026 at 6:04 PM UTC, Tristan Partin wrote:
>> > I noticed that we support various comparison operators on uuid values.
>> > However, we were missing support for the MIN and MAX aggregate
>> > functions, which seems like a logical thing to also support if we
>> > support operators.
>> >
>> > The use case that I envision the most is finding the oldest and newest
>> > UUID v7 values in a set. UUID v7 is a timestamp-prefixed identifier.
>> > According to RFC 9562[0], the first 48 bits of a UUID v7 value are
>> > a Unix Epoch timestamp. Additionally, Postgres implements Method 3 of
>> > Section 6.2[1] for UUID v7 such that the next 12 bits bits store a
>> > 1/4096 (or 2^12) fraction of sub-millisecond precision. See the comment
>> > in generate_uuidv7() for more details.
>> >
>> > [0]: https://datatracker.ietf.org/doc/html/rfc9562#name-uuid-version-7
>> > [1]: https://datatracker.ietf.org/doc/html/rfc9562#monotonicity_counters
>>
>> And of course no patch attached :(.
>
> The intent looks fine to me for UUIDv7. It would be interesting to
> understand why there's been no such support for versions < v7 so far
> in Postgres. Is there a limitation?
Hopefully I understand your question correctly...
I think it was just a miss to not support min and max for uuids.
Postgres supports all versions of uuids in its uuid type, even though it
only supports generating UUIDv4 and UUIDv7:
# SELECT
uuid_extract_version('1f16ff3d-53ae-69a0-be5c-ddeb427ff334'::uuid);
uuid_extract_version
----------------------
6
(1 row)
UUIDv4 is completely random, so min and max don't really make sense to
me, while other variants are also timestamp-prefixed, like UUIDv6 for
instance.
> A minor comment on the patch.
>
> 1/ + UUIDs are compared lexicographically on their 128-bit value.
> For version 7 UUIDs,
>
> How about using UUIDv7 instead of "version 7 UUIDs"?
I think this change probably makes sense. I see we reference UUIDv7 in
the PG 18 release notes[0].
> PostgreSQL 18 also adds UUIDv7 generation through...
Attached is a v2.
[0]: https://www.postgresql.org/about/news/postgresql-18-released-3142/
--
Tristan Partin
PostgreSQL Contributors Team
AWS (https://aws.amazon.com)
From a14757b94468636d31a9075abf6d451e8d66ea39 Mon Sep 17 00:00:00 2001
From: Tristan Partin <[email protected]>
Date: Tue, 23 Jun 2026 17:41:19 +0000
Subject: [PATCH v2] 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 <[email protected]>
---
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 | 10 ++++++++++
src/test/regress/expected/uuid.out | 7 +++++++
src/test/regress/sql/uuid.sql | 3 +++
7 files changed, 52 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 be157a5fbe..3c11fe821a 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,10 @@
{ oid => '2960', descr => 'less-equal-greater',
proname => 'uuid_cmp', proleakproof => 't', prorettype => 'int4',
proargtypes => 'uuid uuid', prosrc => 'uuid_cmp' },
+{ oid => '6519', proname => 'uuid_larger', proleakproof => 't',
+ prorettype => 'uuid', proargtypes => 'uuid uuid', prosrc => 'uuid_larger' },
+{ oid => '6520', 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
