public inbox for [email protected]
help / color / mirror / Atom feedHow can I create a feature request for QUALIFY clause into PostgreSQL?
6+ messages / 4 participants
[nested] [flat]
* How can I create a feature request for QUALIFY clause into PostgreSQL?
@ 2022-10-03 17:38 Onni Hakala <[email protected]>
2022-10-03 18:11 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Laurenz Albe <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Onni Hakala @ 2022-10-03 17:38 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How can I create a feature request for QUALIFY clause into PostgreSQL?
2022-10-03 17:38 How can I create a feature request for QUALIFY clause into PostgreSQL? Onni Hakala <[email protected]>
@ 2022-10-03 18:11 ` Laurenz Albe <[email protected]>
2022-10-03 19:58 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Onni Hakala <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2022-10-03 18:11 UTC (permalink / raw)
To: Onni Hakala <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How can I create a feature request for QUALIFY clause into PostgreSQL?
2022-10-03 17:38 How can I create a feature request for QUALIFY clause into PostgreSQL? Onni Hakala <[email protected]>
2022-10-03 18:11 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Laurenz Albe <[email protected]>
@ 2022-10-03 19:58 ` Onni Hakala <[email protected]>
2022-10-03 20:57 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Tom Lane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Onni Hakala @ 2022-10-03 19:58 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: [email protected]
> 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 protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How can I create a feature request for QUALIFY clause into PostgreSQL?
2022-10-03 17:38 How can I create a feature request for QUALIFY clause into PostgreSQL? Onni Hakala <[email protected]>
2022-10-03 18:11 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Laurenz Albe <[email protected]>
2022-10-03 19:58 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Onni Hakala <[email protected]>
@ 2022-10-03 20:57 ` Tom Lane <[email protected]>
2022-10-04 04:52 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? [email protected]
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2022-10-03 20:57 UTC (permalink / raw)
To: Onni Hakala <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected]
Onni Hakala <[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How can I create a feature request for QUALIFY clause into PostgreSQL?
2022-10-03 17:38 How can I create a feature request for QUALIFY clause into PostgreSQL? Onni Hakala <[email protected]>
2022-10-03 18:11 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Laurenz Albe <[email protected]>
2022-10-03 19:58 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Onni Hakala <[email protected]>
2022-10-03 20:57 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Tom Lane <[email protected]>
@ 2022-10-04 04:52 ` [email protected]
2022-10-04 05:43 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Tom Lane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: [email protected] @ 2022-10-04 04:52 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected]
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 protected]> wrote:
>
> Onni Hakala <[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How can I create a feature request for QUALIFY clause into PostgreSQL?
2022-10-03 17:38 How can I create a feature request for QUALIFY clause into PostgreSQL? Onni Hakala <[email protected]>
2022-10-03 18:11 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Laurenz Albe <[email protected]>
2022-10-03 19:58 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Onni Hakala <[email protected]>
2022-10-03 20:57 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? Tom Lane <[email protected]>
2022-10-04 04:52 ` Re: How can I create a feature request for QUALIFY clause into PostgreSQL? [email protected]
@ 2022-10-04 05:43 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2022-10-04 05:43 UTC (permalink / raw)
To: [email protected]; +Cc: Laurenz Albe <[email protected]>; [email protected]
[email protected] 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-10-04 05:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-10-03 17:38 How can I create a feature request for QUALIFY clause into PostgreSQL? Onni Hakala <[email protected]>
2022-10-03 18:11 ` Laurenz Albe <[email protected]>
2022-10-03 19:58 ` Onni Hakala <[email protected]>
2022-10-03 20:57 ` Tom Lane <[email protected]>
2022-10-04 04:52 ` [email protected]
2022-10-04 05:43 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox