Re: Proposal: QUALIFY clause

On Mon, 21 Jul 2025, 18:31 Tom Lane, <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Matheus Alcantara" <matheusssilv97(at)gmail(dot)com> writes:
> > You're right — semantically, using QUALIFY is equivalent to wrapping the
> > query in a subquery and applying a WHERE clause to the result. The main
> > motivation here is to provide a more ergonomic and readable syntax.
>
> > While I understand the hesitation around introducing another keyword
> > that effectively acts like WHERE at a different stage, I believe QUALIFY
> > improves clarity in many use cases, by avoiding the boilerplate and
> > visual noise of nested subqueries making it easier to write and reason
> > about.
>
> There are concrete reasons not to do this until/unless it becomes
> standardized:
>
> * If the syntax is like WHERE, there will be no way to do it without
> making QUALIFY a fully-reserved word. That will inevitably break
> more than zero applications. It's a lot easier to justify that
> sort of breakage if we can say "QUALIFY is reserved according to
> SQL:20xx, so don't blame us".
>
> * I'm not exactly convinced that the committee would standardize
> it just like this. For one thing, QUALIFY is not even the right
> part of speech: it's a verb, and thus more fit to be a primary
> statement keyword. What you need here is an adverb (I think ...
> been a long time since high school English, but my dictionary
> says WHERE is an adverb). Maybe they'd be persuaded to do what
> the existing implementations did, but I wouldn't be at all surprised
> if they choose a different keyword.
>

I know we're not bikeshedding, but the word REFINE might be more
appropriate.

Thom

>