Re: 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(at)gmail(dot)com>
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.
>
> The idea for this came from a discussion and suggestion by Peter
> Eisentraut (thanks, Peter!).
>
> The `QUALIFY` clause is not part of the SQL standard, but it is
> implemented by some major DBMSs, including Snowflake [1], BigQuery
> [2] and DuckDB [3].
>
> The goal is to provide a more ergonomic way to filter on window function
> results without needing to nest subqueries or CTEs.
>
> Simple example (see window.sql for more):
>
> SELECT depname,
> empno,
> salary,
> RANK() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk
> FROM empsalary
> QUALIFY rnk = 1;
>
> Please note that this is a proof-of-concept patch, I’m still working on
> determining the best locations in the code to implement each part of the
> logic for QUALIFY. I'm just sending this WIP to collect feedback and then
> continue to work on the feature. Additionally, the current patch does not
> handle yet expressions using AND/OR when referencing multiple window
> function aliases (e.g., QUALIFY rnk = 1 AND rnk2 = 2).
>
> Thoughts?
>
> [1] https://docs.snowflake.com/en/sql-reference/constructs/qualify
> [2]
> https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause
> [3] https://duckdb.org/docs/stable/sql/query_syntax/qualify.html
>
> --
> Matheus Alcantara
>