Re: Add MIN/MAX aggregate support for uuid - Mailing list pgsql-hackers

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