PostgreSQL forbids window functions like ROW_NUMBER(), RANK(), or SUM() OVER() in WHERE clauses because window functions execute after WHERE filters. To filter based on window function results, compute them in a subquery or CTE first, then apply your WHERE condition in the outer query.
Window functions in PostgreSQL (such as ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), and others) operate on sets of rows defined by the OVER clause. They are permitted only in the SELECT list and ORDER BY clause of a query, not in WHERE, GROUP BY, or HAVING clauses. This restriction exists because of SQL's logical query execution order: WHERE clauses filter rows before grouping and window calculations occur, so the window function results don't yet exist when WHERE is evaluated. When you try to reference a window function in a WHERE clause, PostgreSQL immediately rejects the query with this error. The standard workaround is to wrap your window function query in a subquery or Common Table Expression (CTE), calculate the window function values there, and then filter on those calculated values in the outer query's WHERE clause.
The most readable solution is to use a Common Table Expression (CTE) with the WITH clause:
-- Instead of this (which fails):
SELECT employee_id, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) <= 10;
-- Do this:
WITH ranked_employees AS (
SELECT employee_id, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
)
SELECT employee_id, salary, salary_rank
FROM ranked_employees
WHERE salary_rank <= 10;The CTE computes the window function in the inner query, then the outer query filters on the calculated column. This approach is clean, maintainable, and works for any window function.
If you don't want to use a CTE, a subquery in the FROM clause achieves the same result:
SELECT employee_id, salary, salary_rank
FROM (
SELECT employee_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
FROM employees
) ranked
WHERE salary_rank = 1;This finds the highest-paid employee in each department by calculating ROW_NUMBER() in the subquery, then filtering where rank equals 1 in the outer query. Subqueries perform identically to CTEs but may be harder to read with complex logic.
PostgreSQL 15 introduced the QUALIFY clause, which allows filtering on window functions without subqueries:
SELECT employee_id, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
QUALIFY ROW_NUMBER() OVER (ORDER BY salary DESC) <= 10;QUALIFY evaluates after window functions are computed, making this the most concise syntax. However, it requires PostgreSQL 15 or later, so check your server version with SELECT version(); before using it. For older versions, stick with CTEs or subqueries.
If you're using ROW_NUMBER() to get the first row per partition, consider DISTINCT ON instead:
-- Instead of:
WITH ranked AS (
SELECT DISTINCT ON (department_id)
employee_id, department_id, salary
FROM employees
ORDER BY department_id, salary DESC
)
SELECT * FROM ranked;
-- This works without window functions:
SELECT DISTINCT ON (department_id)
employee_id, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;DISTINCT ON picks the first row for each unique value of the specified column(s), achieving the same result as ROW_NUMBER() = 1 partitioned queries without needing window functions at all. This only works for simple "top 1 per group" cases.
Window functions execute in the SELECT phase of query processing, after WHERE, GROUP BY, and HAVING but before ORDER BY and LIMIT. This execution order is why they cannot appear in WHERE clauses - the filtered row set must be determined before window calculations can occur. The QUALIFY clause (PostgreSQL 15+) specifically exists to filter on window functions and executes after the window function phase. For complex analytics, consider materialized CTEs (WITH ... AS MATERIALIZED) if you need to reuse expensive window calculations multiple times. When migrating from databases like SQL Server where window functions might work in different contexts, always test queries against PostgreSQL specifically. Performance-wise, CTEs and subqueries with window functions are optimized similarly by PostgreSQL's planner, so choose based on readability rather than performance concerns.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL