Re: Proposal: QUALIFY clause

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(dot)stehule(at)gmail(dot)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(at)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
--