8

Why can I use a UUID to sort rows:

SELECT uuid_nil()
ORDER BY 1;

But I cannot compute the maximum value:

SELECT max(uuid_nil());

[42883] ERROR: function max(uuid) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

I know that I can cast to a character type or ORDER BY and LIMIT 1. I'm just curious as to why I have to use a workaround.

3 Answers 3

8

I guess it's an oversight. To me it doesn't make any sense that you can do >, greatest, least, order by etc. but not do min/max on it. That's just absolutely counter intuitive and not what one would expect.

I define them for me like this:

create function min(uuid, uuid)
    returns uuid
    immutable parallel safe
    language plpgsql as
$$
begin
    return least($1, $2);
end
$$;

create aggregate min(uuid) (
    sfunc = min,
    stype = uuid,
    combinefunc = min,
    parallel = safe,
    sortop = operator (<)
    );

create function max(uuid, uuid)
    returns uuid
    immutable parallel safe
    language plpgsql as
$$
begin
    return greatest($1, $2);
end
$$;

create aggregate max(uuid) (
    sfunc = max,
    stype = uuid,
    combinefunc = max,
    parallel = safe,
    sortop = operator (>)
    );

If you want this to be part of an idempotent migration you'd want to ensure the functions do not exist:

drop aggregate if exists max(uuid);
drop aggregate if exists min(uuid);
drop function if exists min(uuid, uuid);
drop function if exists max(uuid, uuid);
4

I'd say that nobody saw any use in calculating the maximum of UUIDs, and I cannot see one either.

That said, it is easy to use CREATE AGGREGATE to define your own max, if you really need that.

6
  • 1
    I frequently have the use case that I am grouping things and aggregate uuids and need to pick one. I am interested in not picking any but always having a certain tiebreaker, for example just selecting the maximum (which is good enough for me).
    – scravy
    Dec 22, 2021 at 6:49
  • You could also look at DISTINCT ON?
    – Vérace
    Dec 22, 2021 at 7:19
  • 1
    @LaurenzAlbe first_value is a window function, not an aggregate; so I can't use it in a group by for instance.
    – scravy
    Dec 22, 2021 at 7:22
  • @Vérace-getVACCINATEDNOW which doesn't give me reliably and well-defined the same value, see stackoverflow.com/a/59410440/471478
    – scravy
    Dec 22, 2021 at 7:23
  • 1
    I found this useful when doing a large table update in batches when the primary key is a uuid trying to avoid limit-offset. It can then be done in the order of the key and the last updated row id in a batch is max(id) (id_last). Then the next batch starts at id_next > id_last Sep 28, 2023 at 2:06
-7

Because UUID/GUID are not real UUDI/GUID in PostGreSQL, but strings. Lest's see the RFC 4122...

UUIDs are of a fixed size (128 bits) which is reasonably small compared to other alternatives. This lends itself well to sorting, ordering, and hashing of all sorts, storing in databases, simple allocation, and ease of programming in general.

PostGreSQL does respect the datatype of the UUID/GUID and therefore is unable to properly order values ​​of this type (an order in a alphabet is not relative to a binary order)... Because the binary order is not respected any operation like MIN or MAX, will deliver false values and connot operate by construction !

In other databases like Microsoft SQL Server the GUID datatype is really a binary compound of 128 bits an can be ordered as well as MAX/MIN..imized... As an example :

SELECT MAX(NEWID())

This query works on SQL Server...

Worst, using a strings leads to store much more bytes rather using a binary one... Not god for performances !

2
  • 5
    This is completely and utterly false. UUID is a proper type in postgresql which is a 128-bit value, not just the formatted string. See the documentation: postgresql.org/docs/13/datatype-uuid.html
    – scravy
    Dec 22, 2021 at 16:42
  • If so how can you still order by uuid, compare using >, least and greatest? To me what is really odd is that you can do everything else except min and max. Mar 18, 2023 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.