PostgreSQL 22013 Error: Causes and Solutions Complete Guide

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

PostgreSQL error code 22013 (invalid_preceding_or_following_size) is raised when a window function frame is defined with an invalid offset — most commonly a negative number or a NULL value — for PRECEDING or FOLLOWING bounds. This error typically surfaces in dynamic queries or parameterized functions where the offset value is computed at runtime rather than hardcoded.

---

Top 3 Causes

1. Negative Offset Value

The most common cause. PostgreSQL requires frame offsets to be non-negative integers (or valid positive intervals for RANGE mode). Any negative value triggers error 22013 immediately.

-- ❌ This will fail
SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN -3 PRECEDING AND CURRENT ROW  -- negative offset!
    ) AS running_total
FROM employees;
-- ERROR:  invalid preceding or following size in window function

-- ✅ Fix: Use GREATEST() to enforce non-negative values
SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN GREATEST(0, 3 - 5) PRECEDING AND CURRENT ROW
    ) AS safe_running_total
FROM employees;

2. NULL Offset Passed Dynamically

When window frame sizes come from user input, application parameters, or computed columns, a NULL value will trigger the same error. This is especially common in PL/pgSQL functions or dynamically built SQL strings.

-- ❌ NULL passed as window size
CREATE OR REPLACE FUNCTION get_moving_avg(p_size INT)
RETURNS TABLE(sale_date DATE, avg_val NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT s.sale_date,
           AVG(s.revenue) OVER (
               ORDER BY s.sale_date
               ROWS BETWEEN (p_size - 1) PRECEDING AND CURRENT ROW  -- NULL causes error!
           )
    FROM sales s;
END;
$$ LANGUAGE plpgsql;

-- ✅ Fix: Use COALESCE() to provide a safe default
CREATE OR REPLACE FUNCTION get_moving_avg_safe(p_size INT DEFAULT 7)
RETURNS TABLE(sale_date DATE, avg_val NUMERIC) AS $$
DECLARE
    v_safe INT;
BEGIN
    v_safe := GREATEST(COALESCE(p_size, 7), 1);  -- NULL-safe + non-negative

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

3. Negative Interval in RANGE Mode

In RANGE frame mode, interval offsets must also be positive. Using a negative interval — even one that seems logically reasonable like INTERVAL '-7 days' — will throw error 22013.

-- ❌ Negative interval in RANGE mode
SELECT
    order_date,
    revenue,
    SUM(revenue) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '-7 days' PRECEDING AND CURRENT ROW  -- error!
    ) AS weekly_sum
FROM sales;

-- ✅ Fix: Use a positive interval; direction is set by PRECEDING/FOLLOWING
SELECT
    order_date,
    revenue,
    SUM(revenue) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) AS weekly_sum
FROM sales;

-- ✅ Fix for dynamic intervals: use ABS()
DO $$
DECLARE
    v_days INT := -7;
BEGIN
    -- Safe interval computation
    PERFORM SUM(revenue) OVER (
        ORDER BY order_date
        RANGE BETWEEN make_interval(days => ABS(v_days)) PRECEDING AND CURRENT ROW
    )
    FROM sales;
END;
$$;

---

Quick Fix Solutions

Scenario	Fix
Negative integer offset	Wrap with GREATEST(value, 0)
Possible NULL offset	Use COALESCE(value, default)
Negative interval	Apply ABS() then make_interval()
Unknown input type	Combine: GREATEST(COALESCE(val, 0), 0)

---

Prevention Tips

1. Always sanitize dynamic window offsets at the function boundary.
Add a dedicated validation step using COALESCE() and GREATEST() as a standard pattern in every function that accepts window size parameters. Treat window offsets like user input — never trust them to be valid without explicit checks.

-- Reusable safe offset helper
CREATE OR REPLACE FUNCTION safe_offset(p_val INT, p_default INT DEFAULT 1)
RETURNS INT AS $$
    SELECT GREATEST(COALESCE(p_val, p_default), 0);
$$ LANGUAGE sql IMMUTABLE;

2. Include boundary value test cases in your CI/CD pipeline.
Always test window functions with edge case inputs: 0, -1, NULL, and very large integers. Catching 22013 in a test environment is far less costly than in production. Add these cases to your automated regression suite so they run on every deployment.

---

Related Errors

• 42P20 (windowing_error): Triggered by structurally invalid window definitions, such as missing ORDER BY when using RANGE with an offset.
• 22012 (division_by_zero): Can co-occur when dynamic offset calculations involve division.
• 22003 (numeric_value_out_of_range): May appear when offset values overflow PostgreSQL integer bounds.

---

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