Add MIN/MAX aggregate support for uuid

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)

Hi,

On Tue, Jun 23, 2026 at 11:05 AM Tristan Partin <tristan@partin.io> 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?

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"?

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

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 <tristan@partin.io> 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]https://www.postgresql.org/about/news/postgresql-18-released-3142/.

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)

On Wed, Jun 24, 2026 at 10:50 AM Tristan Partin <tristan@partin.io> wrote:

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 <tristan@partin.io> 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 :(.

+1 for the idea. uuid data type is already total-order; it has a bree
opclass and comparison operators, so adding min/max doesn't introduce
new ordering semantics.

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.

The patch mostly looks good to me. One minor comment is:

+{ 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' },

I think we should add the 'decr' to both functions.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

On Wed Jun 24, 2026 at 6:13 PM UTC, Masahiko Sawada wrote:

On Wed, Jun 24, 2026 at 10:50 AM Tristan Partin <tristan@partin.io> wrote:

Attached is a v2.

The patch mostly looks good to me. One minor comment is:

+{ 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' },

I think we should add the 'decr' to both functions.

Any opinions on what the descriptions should be? Here are the equivalent
functions for OID:

{ oid => '1965', descr => 'larger of two',
proname => 'oidlarger', prorettype => 'oid', proargtypes => 'oid oid',
prosrc => 'oidlarger' },
{ oid => '1966', descr => 'smaller of two',
proname => 'oidsmaller', prorettype => 'oid', proargtypes => 'oid oid',
prosrc => 'oidsmaller' },

--
Tristan Partin
PostgreSQL Contributors Team
AWS (https://aws.amazon.com)

Hi,

On Wed, Jun 24, 2026 at 12:13 PM Tristan Partin <tristan@partin.io> wrote:

Any opinions on what the descriptions should be? Here are the equivalent
functions for OID:

{ oid => '1965', descr => 'larger of two',
proname => 'oidlarger', prorettype => 'oid', proargtypes => 'oid oid',
prosrc => 'oidlarger' },
{ oid => '1966', descr => 'smaller of two',
proname => 'oidsmaller', prorettype => 'oid', proargtypes => 'oid oid',
prosrc => 'oidsmaller' },

What you have in the v2 patch looks fine to me. "minimum/maximum value
of all UUID input values" gives it a bit of an "aggregate flavor" as
well - as in, when used in select min(uuid_col) from foo, it returns
the minimum value of all UUID input values.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

On Wed Jun 24, 2026 at 7:47 PM UTC, Bharath Rupireddy wrote:

Hi,

On Wed, Jun 24, 2026 at 12:13 PM Tristan Partin <tristan@partin.io> wrote:

Any opinions on what the descriptions should be? Here are the equivalent
functions for OID:

{ oid => '1965', descr => 'larger of two',
proname => 'oidlarger', prorettype => 'oid', proargtypes => 'oid oid',
prosrc => 'oidlarger' },
{ oid => '1966', descr => 'smaller of two',
proname => 'oidsmaller', prorettype => 'oid', proargtypes => 'oid oid',
prosrc => 'oidsmaller' },

What you have in the v2 patch looks fine to me. "minimum/maximum value
of all UUID input values" gives it a bit of an "aggregate flavor" as
well - as in, when used in select min(uuid_col) from foo, it returns
the minimum value of all UUID input values.

I ended up just using a similar string as oidlarger and bytea_larger.
Although, I added the word "the" to make the descriptions a little more
readable than their counterparts.

"larger of the two" and "smaller of the two"

--
Tristan Partin
PostgreSQL Contributors Team
AWS (https://aws.amazon.com)

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)

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)