From: | Aleksander Alekseev <aleksander(at)timescale(dot)com> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Cc: | Viliam Ďurina <viliam(dot)durina(at)gmail(dot)com> |
Subject: | Re: MIN/MAX functions for a record |
Date: | 2024-03-22 15:02:29 |
Message-ID: | CAJ7c6TNpEfJJSrJvQJmVT5z8=8cPaq-JHNY3ZbmP63rif5Kiow@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Fabrice Chapuis | 2024-03-22 15:05:43 | Re: hot updates and fillfactor |
Previous Message | Robert Haas | 2024-03-22 14:59:30 | Re: psql not responding to SIGINT upon db reconnection |