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.steh...@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. Nico --