Hi,
> In my queries I often need to do MIN/MAX for tuples, for example:
>
> SELECT MAX(row(year, month))
> FROM (VALUES(2025, 1), (2024,2)) x(year, month);
>
> This query throws:
>
> ERROR: function max(record) does not exist
>
> In this case you can replace it with `MAX((year||'-'||month||'-1')::date)`. However in my case I have an event table
with`event_time` and `text` columns, I'm grouping that table by some key and want to have the text for the newest
event.I would do `MAX(ROW(event_time, text)).text`. Workarounds for this are clumsy, e.g. with a subquery with LIMIT 1.
>
> The lack of this feature is kind of unexpected, because the `>` operator or `GREATEST` function are defined for
records:
>
> SELECT
> GREATEST((2025, 1), (2024, 2)),
> (2025, 1) > (2024, 2)
>
> Was this ever discussed or is there something preventing the implementation?
I believe it would be challenging to implement max(record) that would
work reasonably well in a general case.
What if, for instance, one of the columns is JOSNB or XML? Not to
mention the fact that Postgres supports user-defined types which don't
necessarily have a reasonable order. Take a point in a 2D or 3D space
as an example. On top of that I doubt that the proposed query will
perform well since I don't see how it could benefit from using
indexes. I don't claim that this is necessarily true in your case but
generally one could argue that the wrong schema is used here and
instead of (year, month) pair a table should have a date/timestamp(tz)
column.
Personally I would choose format() function [1] in cases like this in
order to play it safe. Assuming of course that the table is small and
the query is not executed often.
[1]: https://www.postgresql.org/docs/current/functions-string.html
--
Best regards,
Aleksander Alekseev