Subject: How can I create a feature request for QUALIFY clause into PostgreSQL?

From: Onni Hakala <[email-masked]>
Date: Mon, 3 Oct 2022 20:38:41 +0300

Hey,

I was very happy learn about QUALIFY clause in BigQuery <https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause> today.

I have used window functions usually with subqueries like this:
SELECT * FROM (
  SELECT
    *,
    ROW_NUMBER OVER (PARTITION BY something ORDER BY modified_date DESC) AS row_number
  FROM table_name
)
WHERE row_number = 1

With QUALIFY it's much more ergonomic and cleaner to do the same thing.
SELECT *
FROM table_name
QUALIFY ROW_NUMBER() OVER (PARTITION BY something ORDER BY modified_date DESC) = 1

QUALIFY is also better since it doesn't include extra column row_number to the result.

Where should I send message so that Postgres maintainers would consider adding this into the TODO page: https://wiki.postgresql.org/wiki/Todo <https://wiki.postgresql.org/wiki/Todo>

Thanks in advance,
Onni Hakala

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 03 Oct 2022 20:11:17 +0200

On Mon, 2022-10-03 at 20:38 +0300, Onni Hakala wrote:
> I was very happy learn about QUALIFY clause in BigQuery today.
> 
> Where should I send message so that Postgres maintainers would consider adding this into the TODO page: https://wiki.postgresql.org/wiki/Todo

PostgreSQL supports DISTICT ON, which can do this even more ergonomically.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

---

From: Onni Hakala <[email-masked]>
Date: Mon, 3 Oct 2022 22:58:52 +0300

> PostgreSQL supports DISTICT ON, which can do this even more ergonomically.
Yes using DISTINCT ON one can solve some usecases but not nearly all.

I should have selected better example here in the first place.

For example I created db-fiddle of this email conversation here: https://www.db-fiddle.com/f/g2QXCdZqoaXc9XP8mHgaJT/1 <https://www.db-fiddle.com/f/g2QXCdZqoaXc9XP8mHgaJT/1>

Can you show me how you could use that to get the latest 2 events from the events table using DISTINCT ON?

CREATE TABLE events (
    person TEXT,
    event_type TEXT ,
    created_at TIMESTAMP
);

INSERT INTO events VALUES 
  ('onni.hakala','message',now()),
  ('laurenz.albe','non-helpful reply',now() + interval '30 minutes'),
  ('onni.hakala','clarifying reply',now() + interval '35 minutes'),
  ('someone.else','other reply',now() + interval '40 minutes'),
  ('onni.hakala','other reply',now() + interval '45 minutes'),
  ('someone.else','other reply',now() + interval '50 minutes');

I can write this:

SELECT * FROM (
	SELECT
		*,
  		ROW_NUMBER() OVER (PARTITION BY person ORDER BY created_at DESC) AS row_number
	FROM events
) sub
WHERE row_number <= 2

But what I would want to write is this:

SELECT *
FROM events
QUALIFY OVER (PARTITION BY person ORDER BY created_at DESC) AS <= 2

> On 3. Oct 2022, at 21:11, Laurenz Albe <[email-masked]> wrote:
>

---

From: Tom Lane <[email-masked]>
Date: Mon, 03 Oct 2022 16:57:40 -0400

> Can you show me how you could use that to get the latest 2 events from the events table using DISTINCT ON?

I assume you mean latest 2 events per person, else it's trivially
solved with ORDER BY ... LIMIT 2.  But I'd still be inclined to
solve it with ORDER BY ... LIMIT:

=> select e.* from
  (select distinct person from events) p
  cross join lateral
  (select e.* from events e where p.person = e.person
   order by created_at desc limit 2) e;
    person    |    event_type     |         created_at         
--------------+-------------------+----------------------------
 laurenz.albe | non-helpful reply | 2022-10-03 17:16:39.957743
 someone.else | other reply       | 2022-10-03 17:36:39.957743
 someone.else | other reply       | 2022-10-03 17:26:39.957743
 onni.hakala  | other reply       | 2022-10-03 17:31:39.957743
 onni.hakala  | clarifying reply  | 2022-10-03 17:21:39.957743
(5 rows)

In a real application you could probably avoid the SELECT DISTINCT
by joining to some other table that has just one row per person.

			regards, tom lane

---

From: [email-masked]
Date: Tue, 4 Oct 2022 07:52:03 +0300

Thanks Tom for showing me a different way to think about this problem and yes I meant the latest 2 events per person. And anyway I can just use a subquery for this too as shown in my earlier emails. The ergonomics of your cross join lateral don't seem that much better the subquery.

This still doesn't answer the original question of how to ask for new features in Postgres.

Best regards,
Onni Hakala

> On 3. Oct 2022, at 23:57, Tom Lane <[email-masked]> wrote:
>

---

From: Tom Lane <[email-masked]>
Date: Tue, 04 Oct 2022 01:43:46 -0400

> This still doesn't answer the original question of how to ask for new features in Postgres.

Well, you post on the mailing lists ;-)

I don't think this particular request is going anywhere.  We're not
terribly receptive to non-SQL-standard syntax if there's already
other ways to get the same thing done.  New syntax has large costs:
not only the initial implementation, but documentation, ongoing
maintenance, and incremental slowdown of the parser.  Plus, if
it's something that's not in the SQL standard, there's a big risk
of getting blindsided by incompatible future extensions of the
standard.  Having said all that, really compelling ideas might
seduce us anyway ... but this specific idea seems ugly and
nonintuitive as well as nonstandard.

			regards, tom lane