Thread: How can I create a feature request for QUALIFY clause into PostgreSQL?
Hey,
I was very happy learn about QUALIFY clause in BigQuery 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
Thanks in advance,
Onni Hakala
On Mon, 2022-10-03 at 20:38 +0300, Onni Hakala wrote: > I was very happy learn about QUALIFY clause in BigQuery 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 PostgreSQL supports DISTICT ON, which can do this even more ergonomically. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
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
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 <laurenz.albe@cybertec.at> wrote:On Mon, 2022-10-03 at 20:38 +0300, Onni Hakala wrote:I was very happy learn about QUALIFY clause in BigQuery 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
PostgreSQL supports DISTICT ON, which can do this even more ergonomically.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Onni Hakala <onni@keksi.io> writes:
> 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
Thanks Tom for showing me a different way to think about this problem and yes I meant the latest 2 events per person. Andanyway I can just use a subquery for this too as shown in my earlier emails. The ergonomics of your cross join lateraldon'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 <tgl@sss.pgh.pa.us> wrote: > > Onni Hakala <onni@keksi.io> writes: >> 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
onni@keksi.io writes:
> 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