Proposal: QUALIFY clause

Many times I have thought it would be nice if there was a QUALIFY clause in
Postgres!

Just would like to add that including your list, Teradata, Redshift, SAP
HANA, HP Vertica, and Trino all support the QUALIFY clause.

Also it seems Postgres would be the first leading RDBMS - meaning like
traditional, multipurpose RDMBS - to support QUALIFY, which would be pretty
cool.

On Mon, Jul 21, 2025 at 7:47 AM Matheus Alcantara <matheusssilv97@gmail.com>
wrote:

On Mon, 21 Jul 2025 at 10:19, Mike Artz <michaeleartz@gmail.com> wrote:

Many times I have thought it would be nice if there was a QUALIFY clause
in Postgres!

Just would like to add that including your list, Teradata, Redshift, SAP
HANA, HP Vertica, and Trino all support the QUALIFY clause.

Also it seems Postgres would be the first leading RDBMS - meaning like
traditional, multipurpose RDMBS - to support QUALIFY, which would be pretty
cool.

Is this different from using the window functions in a subquery and then
applying a WHERE clause on the outer query?

SELECT … FROM (SELECT … [including window functions] FROM …) WHERE [stuff
that would be in QUALIFY]

I'll be honest, I'm skeptical that we need another keyword that basically
means “WHERE, but applied at a different point in the query processing”.
I'm not even convinced that HAVING was a good idea (although obviously I
would not propose removal).

Isaac Morland <isaac.morland@gmail.com> writes:

I'll be honest, I'm skeptical that we need another keyword that basically
means “WHERE, but applied at a different point in the query processing”.

That was my reaction too. I'm especially skeptical that getting out
front of the SQL standards committee is a good thing to do. If and
when this shows up in the standard, then sure.

regards, tom lane

On Mon Jul 21, 2025 at 11:29 AM -03, Isaac Morland wrote:

Is this different from using the window functions in a subquery and then
applying a WHERE clause on the outer query?

SELECT … FROM (SELECT … [including window functions] FROM …) WHERE [stuff
that would be in QUALIFY]

I'll be honest, I'm skeptical that we need another keyword that basically
means “WHERE, but applied at a different point in the query processing”.
I'm not even convinced that HAVING was a good idea (although obviously I
would not propose removal).

Thanks for sharing your thoughts!

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.

--
Matheus Alcantara

"Matheus Alcantara" <matheusssilv97@gmail.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.

regards, tom lane

On Mon, 21 Jul 2025, 18:31 Tom Lane, <tgl@sss.pgh.pa.us> wrote:

"Matheus Alcantara" <matheusssilv97@gmail.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

On Mon, Jul 21, 2025 at 9:32 PM Thom Brown <thom@linux.com> wrote:

* 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.

I vote for DONTGIMMEDAT.

.m

On 21/07/2025 16:41, Tom Lane wrote:

Isaac Morland <isaac.morland@gmail.com> writes:

I'll be honest, I'm skeptical that we need another keyword that basically
means “WHERE, but applied at a different point in the query processing”.

That was my reaction too. I'm especially skeptical that getting out
front of the SQL standards committee is a good thing to do. If and
when this shows up in the standard, then sure.

It's "when", not "if".  I submitted a paper for this to the committee
two years ago, but it was just a discussion paper and not an actual
change proposal.  I have recently revived that paper so hopefully it
will be accepted within the next year.  I would even like to push so
that we have it in 19.

--

Vik Fearing

On 21/07/2025 14:47, Matheus Alcantara wrote:

Hi all,

I'm sending a proof-of-concept patch to add support for the QUALIFY
clause in Postgres. This feature allows filtering rows after window
functions are computed, using a syntax similar to the WHERE or HAVING
clauses.

I took a very brief look at this, and I think your grammar is wrong. 
The QUALIFY clause should go after the WINDOW clause, just like
FROM/WHERE and GROUP BY/HAVING.

That is what I am proposing to the standards committee, and I already
have some buy-in for that.

--

Vik Fearing

On 21/07/2025 19:30, Tom Lane wrote:

"Matheus Alcantara" <matheusssilv97@gmail.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".

Yes, it will need to be reserved.

* 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 am pretty sure that the keyword will be QUALIFY. There are just too
many existing implementations for the standard to go against them all. 
(Also, another rdbms just implemented it that way in their upcoming
product.)

I agree that we should hold back until the standard accepts it, but
having a working patch ready to go seems like a good idea.

--

Vik Fearing

On Mon, Jul 21, 2025 at 10:26:51PM +0200, Vik Fearing wrote:

On 21/07/2025 19:30, Tom Lane wrote:

* 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.

Or a gerund, which is what HAVING is. Or a conjugated verb or something
like QUALIFIED BY, though really "qualif*" seems just wrong. This is
just another name for a WHERE that, like HAVING is paired with some
other language feature (like GROUP BY) and applies to that clause. I
don't have a better keyword(s) to offer, just sadness.

I am pretty sure that the keyword will be QUALIFY. There are just too many
existing implementations for the standard to go against them all.  (Also,
another rdbms just implemented it that way in their upcoming product.)

Bummer.

On Mon Jul 21, 2025 at 5:23 PM -03, Vik Fearing wrote:

On 21/07/2025 14:47, Matheus Alcantara wrote:

Hi all,

I'm sending a proof-of-concept patch to add support for the QUALIFY
clause in Postgres. This feature allows filtering rows after window
functions are computed, using a syntax similar to the WHERE or HAVING
clauses.

I took a very brief look at this, and I think your grammar is wrong. 
The QUALIFY clause should go after the WINDOW clause, just like
FROM/WHERE and GROUP BY/HAVING.

That is what I am proposing to the standards committee, and I already
have some buy-in for that.

Thank you for the brief review and for the comments!

I'm not sure if I fully understand but please see the new attached
version.

Thanks,

--
Matheus Alcantara

On 21/07/2025 23:29, Matheus Alcantara wrote:

On Mon Jul 21, 2025 at 5:23 PM -03, Vik Fearing wrote:

On 21/07/2025 14:47, Matheus Alcantara wrote:

Hi all,

I'm sending a proof-of-concept patch to add support for the QUALIFY
clause in Postgres. This feature allows filtering rows after window
functions are computed, using a syntax similar to the WHERE or HAVING
clauses.

I took a very brief look at this, and I think your grammar is wrong.
The QUALIFY clause should go after the WINDOW clause, just like
FROM/WHERE and GROUP BY/HAVING.

That is what I am proposing to the standards committee, and I already
have some buy-in for that.

Thank you for the brief review and for the comments!

I'm not sure if I fully understand but please see the new attached
version.

That is my preferred grammar, thank you.  I have not looked at the C
code by this can be obtained with a syntax transformation. To wit:

SELECT a, b, c
FROM tab
QUALIFY wf() OVER () = ?

can be rewritten as:

SELECT a, b, c
FROM (
    SELECT a, b, c, wf() OVER () = ? AS qc
    FROM tab
) AS q
WHERE qc

and then let the optimizer take over.  The standard does this kind of
thing all over the place; I don't know what the postgres project's
position on doing things like this are.

--

Vik Fearing

Vik Fearing <vik@postgresfriends.org> writes:

That is my preferred grammar, thank you. I have not looked at the C
code by this can be obtained with a syntax transformation. To wit:

SELECT a, b, c
FROM tab
QUALIFY wf() OVER () = ?

can be rewritten as:

SELECT a, b, c
FROM (
    SELECT a, b, c, wf() OVER () = ? AS qc
    FROM tab
) AS q
WHERE qc

That answers another question I was going to raise. Matheus's
opening example was

SELECT depname,
empno,
salary,
RANK() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk
FROM empsalary
QUALIFY rnk = 1;

which implies that the QUALIFY clause sees the SELECT output columns,
and hence that it can't use any values not emitted by the SELECT list.
Your transformation implies that it sees the same namespace as the
SELECT list, which seems like a much better and less confusing
definition to me.

regards, tom lane

Hi

út 22. 7. 2025 v 0:12 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 21/07/2025 23:29, Matheus Alcantara wrote:

On Mon Jul 21, 2025 at 5:23 PM -03, Vik Fearing wrote:

On 21/07/2025 14:47, Matheus Alcantara wrote:

Hi all,

I'm sending a proof-of-concept patch to add support for the QUALIFY
clause in Postgres. This feature allows filtering rows after window
functions are computed, using a syntax similar to the WHERE or HAVING
clauses.

I took a very brief look at this, and I think your grammar is wrong.
The QUALIFY clause should go after the WINDOW clause, just like
FROM/WHERE and GROUP BY/HAVING.

That is what I am proposing to the standards committee, and I already
have some buy-in for that.

Thank you for the brief review and for the comments!

I'm not sure if I fully understand but please see the new attached
version.

That is my preferred grammar, thank you. I have not looked at the C
code by this can be obtained with a syntax transformation. To wit:

SELECT a, b, c
FROM tab
QUALIFY wf() OVER () = ?

can be rewritten as:

SELECT a, b, c
FROM (
SELECT a, b, c, wf() OVER () = ? AS qc
FROM tab
) AS q
WHERE qc

and then let the optimizer take over. The standard does this kind of
thing all over the place; I don't know what the postgres project's
position on doing things like this are.

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.

Regards

Pavel

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

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.

Nico
--

On Mon, Jul 21, 2025 at 10:08 PM Nico Williams <nico@cryptonector.com>
wrote:

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.

merlin

Nico Williams <nico@cryptonector.com> writes:

On Mon, Jul 21, 2025 at 09:43:15PM -0600, Merlin Moncure wrote:

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.

No, it's really quite wrong. Aggregate functions are not equivalent
to window functions: if you have both in a query, they execute in
separate passes, with the window functions operating on the grouped
rows output by the aggregation step (and then filtered by HAVING,
if any).

If we're going to support this, it does need to be its own clause.

regards, tom lane