Horton

Re: Proposal: QUALIFY clause

Merlin Moncure <mmoncure@gmail.com>

View thread
From: Merlin Moncure <mmoncure@gmail.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Vik Fearing <vik@postgresfriends.org>, Matheus Alcantara <matheusssilv97@gmail.com>, Pg Hackers <pgsql-hackers@postgresql.org>, Peter Eisentraut <peter@eisentraut.org>
Date: 2025-07-22T03:43:15Z
Lists: pgsql-hackers 
On Mon, Jul 21, 2025 at 9:19 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

>
> just for curiosity - why the HAVING clause was not used?
>
> Any window functions are +/- an "aggregate" function, and then HAVING
> looks more natural to me.
>

Hm, HAVING requires to apply 'group by' which windows functions do not
require (unlike aggregates).

superuser@postgres=# select * from (select 1 as v) q having true limit 1;
ERROR:  column "q.v" must appear in the GROUP BY clause or be used in an
aggregate function
LINE 1: select * from (select 1 as v) q having true limit 1;

If a query has both window function and grouped aggregate, HAVING would be
applying at different grains potentially? If so, seems sus.

merlin