patternsqlMinor

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

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

whyfunctionminmaxuuidthere

Problem

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.

Solution

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

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

Code Snippets

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

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

Context

StackExchange Database Administrators Q#275251, answer score: 8

Revisions (0)

No revisions yet.