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 :(. -- Tristan Partin PostgreSQL Contributors Team AWS (https://aws.amazon.com)
From bab5f2bc51e4751f5ac1ef8bf1ea3a7db7b770a5 Mon Sep 17 00:00:00 2001 From: Tristan Partin <[email protected]> Date: Tue, 23 Jun 2026 17:41:19 +0000 Subject: [PATCH v1] 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..688be551ea 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 version 7 UUIDs, + 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
