PostgreSQL 42P20 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42P20: windowing error

PostgreSQL error code 42P20 — windowing error — occurs when a window function is used incorrectly within a SQL query. This typically happens when the OVER() clause contains an invalid frame specification, incompatible data types, or when window functions are placed in clauses where they are not permitted (such as WHERE or HAVING).

Top 3 Causes

1. Invalid Frame Clause Boundaries

The most common cause is specifying a frame where the start boundary comes after the end boundary — which is logically impossible.

-- ❌ Bad: CURRENT ROW cannot precede UNBOUNDED PRECEDING
SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING
    ) AS bad_total
FROM employees;
-- ERROR:  42P20: frame starting from current row cannot have preceding rows

-- ✅ Good: Start boundary must be <= end boundary
SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM employees;

2. Using Window Functions in WHERE or HAVING Clauses

Window functions are evaluated after the result set is formed, so they cannot be used directly in WHERE or HAVING clauses.

-- ❌ Bad: Window function in WHERE clause
SELECT employee_id, salary
FROM employees
WHERE RANK() OVER (ORDER BY salary DESC) <= 5;
-- ERROR: window functions not allowed in WHERE clause

-- ✅ Good: Wrap in a CTE or subquery
WITH ranked AS (
    SELECT
        employee_id,
        salary,
        RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT employee_id, salary, rnk
FROM ranked
WHERE rnk <= 5;

3. RANGE Frame with Incompatible ORDER BY Column Type

When using RANGE BETWEEN ... PRECEDING/FOLLOWING with a numeric offset, the ORDER BY column must be a numeric or date/time type. Using a text column triggers 42P20.

-- ❌ Bad: Text column with numeric RANGE offset
SELECT
    employee_name,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_name        -- text type: not allowed with offset
        RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
    )
FROM employees;
-- ERROR: 42P20: RANGE with offset PRECEDING/FOLLOWING requires
--        a single ORDER BY column of numeric or date/time type

-- ✅ Fix 1: Switch to ROWS (physical row-based frame)
SELECT
    employee_name,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_name
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS group_sum
FROM employees;

-- ✅ Fix 2: Use a numeric/date column with RANGE
SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) AS rolling_7day_sum
FROM orders;

Quick Fix Checklist

Symptom	Fix
Frame boundary error	Ensure start ≤ end (`UNBOUNDED PRECEDING → CURRENT ROW`)
Window function in `WHERE`	Move to CTE or subquery
`RANGE` offset type mismatch	Use `ROWS` instead, or change `ORDER BY` column to numeric/date

Prevention Tips

1. Always use CTEs to isolate window function logic.
Separating window calculations from filtering logic prevents placement errors and makes queries easier to debug and maintain.

-- Clean, maintainable pattern
WITH windowed AS (
    SELECT
        department_id,
        employee_id,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rn,
        AVG(salary) OVER (
            PARTITION BY department_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS dept_avg
    FROM employees
)
SELECT *
FROM windowed
WHERE rn = 1;

2. Know your frame types: ROWS vs RANGE vs GROUPS.
Default to ROWS for predictable behavior. Use RANGE only when you explicitly need value-based boundaries with numeric or date ORDER BY columns. When in doubt, test your frame clause on a small dataset before deploying to production.

Related Errors

42803 grouping_error — Triggered by misuse of aggregate functions; often appears alongside 42P20 when mixing window and aggregate functions incorrectly.
42601 syntax_error — Caught at parse time before 42P20; indicates malformed OVER() clause syntax.
0A000 feature_not_supported — Raised when using a window function feature not available in your current PostgreSQL version.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community