PostgreSQL 22013 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22013: Invalid Preceding or Following Size in Window Function

PostgreSQL error 22013 occurs when the offset value specified in the PRECEDING or FOLLOWING clause of a window function frame is invalid. This typically happens when the offset is negative, NULL, or of an incompatible data type. The database engine cannot construct a meaningful window frame with such values and immediately raises this error.

Top 3 Causes

1. Negative Offset Value

The most common cause is passing a negative integer as a frame offset. SQL standards and PostgreSQL both require that PRECEDING and FOLLOWING offsets must be zero or a positive integer.

-- ERROR: negative offset causes 22013
SELECT
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY hire_date
        ROWS BETWEEN -2 PRECEDING AND CURRENT ROW  -- invalid!
    ) AS avg_salary
FROM employees;

-- FIXED: use a non-negative integer
SELECT
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY hire_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS avg_salary
FROM employees;

2. NULL Offset Value

When the offset is computed dynamically (e.g., from a subquery, parameter, or expression), it can evaluate to NULL and trigger error 22013.

-- ERROR: NULL offset causes 22013
WITH cfg AS (
    SELECT NULL::INTEGER AS win_size
)
SELECT
    order_id,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN (SELECT win_size FROM cfg) PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

-- FIXED: use COALESCE to provide a safe default
WITH cfg AS (
    SELECT COALESCE(NULL::INTEGER, 7) AS win_size
)
SELECT
    order_id,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN (SELECT win_size FROM cfg) PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

3. Non-Integer Offset in ROWS Mode or Type Mismatch in RANGE Mode

Using a fractional value like 1.5 in ROWS BETWEEN or providing an offset with a type incompatible with the ORDER BY column in RANGE BETWEEN will also raise this error.

-- ERROR: fractional offset in ROWS mode
SELECT
    sale_id,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 1.5 PRECEDING AND CURRENT ROW  -- invalid!
    ) AS total
FROM sales;

-- FIXED (ROWS mode): use an integer
SELECT
    sale_id,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS total
FROM sales;

-- FIXED (RANGE mode): match the offset type to the ORDER BY column type
SELECT
    sale_id,
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) AS weekly_total
FROM sales;

Quick Fix Solutions

Always validate dynamic offsets before passing them to window functions.
Use GREATEST(computed_value, 0) to guarantee a non-negative offset.
Use COALESCE(computed_value, fallback_integer) to handle NULLs.
Encapsulate window function logic inside a stored function with input validation.

-- Defensive helper pattern
CREATE OR REPLACE FUNCTION safe_moving_avg(p_days INTEGER DEFAULT 7)
RETURNS TABLE(sale_date DATE, moving_avg NUMERIC) AS $$
BEGIN
    IF p_days IS NULL OR p_days < 0 THEN
        RAISE EXCEPTION 'Window size must be a non-negative integer, got: %', p_days;
    END IF;

    RETURN QUERY
    SELECT
        s.sale_date,
        AVG(s.amount) OVER (
            ORDER BY s.sale_date
            ROWS BETWEEN p_days PRECEDING AND CURRENT ROW
        )
    FROM sales s
    ORDER BY s.sale_date;
END;
$$ LANGUAGE plpgsql;

Prevention Tips

Validate all dynamic window offsets at the application or function layer. Never trust that a computed value will be a valid positive integer. Apply GREATEST(..., 0) and COALESCE(...) guards consistently, and consider raising a descriptive exception for invalid inputs rather than letting PostgreSQL surface a cryptic error.
Document and enforce ROWS vs. RANGE usage in your team's SQL coding standards. Clearly specify that ROWS mode requires integer offsets and RANGE mode requires offsets whose type matches the ORDER BY column. Including this rule in code review checklists significantly reduces the occurrence of error 22013 in production environments.

Related Errors

42P20 (windowing_error) – Raised when the window function definition itself is structurally invalid.
22000 (data_exception) – The parent error class for 22013 and other data-related exceptions.
42883 (undefined_function) – Can occur in RANGE mode when no operator exists for the given offset type combination.

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