On Mon, Jul 21, 2025 at 09:43:15PM -0600, Merlin Moncure wrote: > 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).
Pavel's point is precisely to allow HAVING w/o a GROUP BY when there are window functions since window functions are "+/-" ("more or less") aggregate functions. That makes sense to me.
> 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.
I would have a HAVING clause that comes _before_ GROUP BY apply to window functions and a second one that comes _after_ GROUP BY apply to the grouping.
I don't know...consider:
#1 SELECT lag(1) OVER(), max(v) FROM (SELECT 1 AS v) q HAVING true ;
...
#2 SELECT lag(1) OVER(), max(v) FROM (SELECT 1 AS v) q HAVING true HAVING lag(1) OVER() IS NULL;
What does the HAVING clause apply to in #1? I think you might be in trouble with the standard here. 2nd clause doesn't feel right in #2. The basic problem is that HAVING does more than just 'syntax sugar subquery / WHERE' and it just can't be hijacked to do something else IMO.
Syntax simplifying
SELECT * FROM (<window function query>) WHERE col = x
Does have some merit, but implementing non-standard syntax has risks, especially in this area of the grammar. If you did do it, I'd vote for QUALIFY since implementation consensus seems to influence the standard to some degree, but I have to unfortunately +1 the 'reserved word' warning. You could probably work around that with more complex syntax but that kind of defeats the point.
