MIN/MAX functions for a record

From: Viliam Ďurina <viliam(dot)durina(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: MIN/MAX functions for a record
Date: 2024-03-22 11:26:27
Message-ID: CAO=iB8L4WYSNxCJ8GURRjQsrXEQ2-zn3FiCsh2LMqvWq2WcONg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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?

Viliam

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Sergey Prokhorenko 2024-03-22 11:43:58 Re: UUID v7
Previous Message Ashutosh Bapat 2024-03-22 10:48:38 Re: Memory consumed by child SpecialJoinInfo in partitionwise join planning