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