PostgreSQL does not allow window functions to be nested inside the arguments of other window functions. If you need to apply multiple window functions sequentially, use a CTE or subquery to compute the inner window function first, then reference that result in the outer window function.
Window functions in PostgreSQL (such as ROW_NUMBER(), RANK(), SUM() OVER(), etc.) cannot contain another window function in their arguments. This restriction is enforced by the parser in the parse_agg.c module, which checks that window function calls are not nested. Unlike aggregate functions (which can appear inside window function arguments), window functions are fundamentally incompatible with nesting due to how PostgreSQL's query planner evaluates them. When you attempt to write an expression like SELECT ROW_NUMBER() OVER (ORDER BY SUM(x) OVER (PARTITION BY y)), PostgreSQL immediately rejects it with "window function calls cannot be nested". The solution is to break the query into stages using a subquery or CTE, computing each window function separately and then referencing the result in the next stage.
Examine your query and locate the window function that contains another OVER clause inside its arguments. The error message will point to the problematic expression. For example, in:
SELECT ROW_NUMBER() OVER (ORDER BY SUM(salary) OVER (PARTITION BY department))
FROM employees;The inner SUM(salary) OVER (PARTITION BY department) is nested inside the outer ROW_NUMBER()'s ORDER BY clause.
The most readable solution is to extract the inner window function into a CTE:
-- INVALID: nested window functions
SELECT ROW_NUMBER() OVER (ORDER BY SUM(salary) OVER (PARTITION BY department))
FROM employees;
-- VALID: compute inner window function first
WITH dept_salaries AS (
SELECT *, SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees
)
SELECT *, ROW_NUMBER() OVER (ORDER BY dept_total)
FROM dept_salaries;The CTE computes the inner window function (SUM over department) in the first stage, then the outer query applies ROW_NUMBER() over the calculated values. This approach is clean and maintainable.
If you need to apply multiple levels of window functions, stack CTEs:
-- INVALID: multiple nested window functions
SELECT
employee_id,
RANK() OVER (ORDER BY ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary))
FROM employees;
-- VALID: layer CTEs
WITH ranked_by_dept AS (
SELECT
employee_id,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees
),
company_rank AS (
SELECT
employee_id,
department,
salary,
dept_rank,
RANK() OVER (ORDER BY salary DESC) AS company_rank
FROM ranked_by_dept
)
SELECT * FROM company_rank;Each CTE stage computes one window function, and subsequent CTEs reference the calculated columns from previous stages.
If you prefer inline syntax instead of WITH clauses, subqueries in the FROM clause work identically:
SELECT
*,
RANK() OVER (ORDER BY dept_sum) AS company_rank
FROM (
SELECT
*,
SUM(salary) OVER (PARTITION BY department) AS dept_sum
FROM employees
) ranked_data;Subqueries achieve the same result as CTEs but may be harder to read with deeply nested levels. Choose based on query complexity and readability.
It is valid to use aggregate functions (SUM, COUNT, AVG, MAX, MIN) inside window function arguments:
-- VALID: aggregate inside window function
SELECT
employee_id,
SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;
-- INVALID: window function inside window function
SELECT
employee_id,
RANK() OVER (ORDER BY SUM(salary) OVER (PARTITION BY department))
FROM employees;If you encounter nesting errors, verify that the innermost function is a regular aggregate, not a window function.
After refactoring with CTEs, test each intermediate stage separately:
-- Test stage 1: inner window function
WITH dept_salaries AS (
SELECT *, SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees
)
SELECT * FROM dept_salaries;
-- Then add stage 2 with outer window function
WITH dept_salaries AS (
SELECT *, SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees
),
ranked AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY dept_total) AS rn
FROM dept_salaries
)
SELECT * FROM ranked;This incremental approach helps isolate where errors occur and validates each window function independently.
PostgreSQL's window function parser enforces non-nesting as a fundamental restriction defined in the SQL:2008 standard. The check occurs in parse_agg.c before query execution, so no amount of data manipulation will work around it. Unlike some other databases (SQL Server, Oracle) which support nested window functions or have workarounds, PostgreSQL requires refactoring into stages. The parser comment itself states: 'A window function call can't contain another one (but aggs are OK).' Importantly, aggregate functions like SUM, COUNT, and AVG ARE allowed inside window function OVER clauses because they operate on the window frame's data before the window function ranks or calculates. The key distinction is that aggregates reduce the window frame to single values, while window functions produce one output row per input row, making the nesting semantically invalid. When optimizing for performance, note that CTEs with window functions are inlined by PostgreSQL's optimizer (unless explicitly materialized), so multiple CTEs incur no performance penalty versus subqueries.
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