Why is there no max(uuid)/min(uuid) function?

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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 !