Re: Add MIN/MAX aggregate support for uuid

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(at)partin(dot)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].

> 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)