On Wed Jun 24, 2026 at 9:32 PM UTC, Zsolt Parragi wrote: > Doesn't opr_sanity.out also require an update? It's failing for me > locally because of the new leakproof functions. (I verified it with v2 > which also had the missing descriptions as a diff here, which should > be solved now, but the other diff should be still there with v3)
Thanks. I was unaware of this test. Fixed in v4. -- Tristan Partin PostgreSQL Contributors Team AWS (https://aws.amazon.com)
From f37e51d0effbea19c39bee391e922b6f27e1b2df Mon Sep 17 00:00:00 2001 From: Tristan Partin <[email protected]> Date: Tue, 23 Jun 2026 17:41:19 +0000 Subject: [PATCH v4] 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 | 12 ++++++++++++ src/test/regress/expected/opr_sanity.out | 2 ++ src/test/regress/expected/uuid.out | 7 +++++++ src/test/regress/sql/uuid.sql | 3 +++ 8 files changed, 56 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/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 80400a3373..85a37effc2 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -889,6 +889,8 @@ oid8ge(oid8,oid8) btoid8cmp(oid8,oid8) tid_block(tid) tid_offset(tid) +uuid_larger(uuid,uuid) +uuid_smaller(uuid,uuid) -- 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/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
