Horton

Thread

Commits

  1. Add min() and max() aggregate support for uuid.

  1. Add MIN/MAX aggregate support for uuid

    Tristan Partin <tristan@partin.io> — 2026-06-23T18:03:51Z

    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

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

  2. Re: Add MIN/MAX aggregate support for uuid

    Tristan Partin <tristan@partin.io> — 2026-06-23T18:05:34Z

    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)

  3. Re: Add MIN/MAX aggregate support for uuid

    Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> — 2026-06-24T05:58:47Z

    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

  4. Re: Add MIN/MAX aggregate support for uuid

    Tristan Partin <tristan@partin.io> — 2026-06-24T17:50:30Z

    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].

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

  5. Re: Add MIN/MAX aggregate support for uuid

    Masahiko Sawada <sawada.mshk@gmail.com> — 2026-06-24T18:12:47Z

    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

  6. Re: Add MIN/MAX aggregate support for uuid

    Tristan Partin <tristan@partin.io> — 2026-06-24T19:13:36Z

    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)

  7. Re: Add MIN/MAX aggregate support for uuid

    Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> — 2026-06-24T19:47:13Z

    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

  8. Re: Add MIN/MAX aggregate support for uuid

    Tristan Partin <tristan@partin.io> — 2026-06-24T21:26:10Z

    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)

  9. Re: Add MIN/MAX aggregate support for uuid

    Zsolt Parragi <zsolt.parragi@percona.com> — 2026-06-24T21:32:08Z

    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)

  10. Re: Add MIN/MAX aggregate support for uuid

    Tristan Partin <tristan@partin.io> — 2026-06-24T21:54:00Z

    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)

  11. Re: Add MIN/MAX aggregate support for uuid

    Masahiko Sawada <sawada.mshk@gmail.com> — 2026-06-30T18:16:54Z

    On Wed, Jun 24, 2026 at 2:54 PM Tristan Partin <tristan@partin.io> wrote:
>
> 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.

The patch looks good to me. I'm going to push it barring further comments.

Regards,


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

  12. Re: Add MIN/MAX aggregate support for uuid

    Masahiko Sawada <sawada.mshk@gmail.com> — 2026-07-01T20:37:15Z

    On Tue, Jun 30, 2026 at 11:16 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Wed, Jun 24, 2026 at 2:54 PM Tristan Partin <tristan@partin.io> wrote:
> >
> > 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.
>
> The patch looks good to me. I'm going to push it barring further comments.
>

Pushed.

Regards,

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