PostgreSQL 22016 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22016: Invalid Argument for nth_value Function

PostgreSQL error code 22016 is thrown when the nth_value() window function receives an invalid second argument. The nth_value(value, n) function returns the value from the Nth row within the current window frame, and n must always be a positive integer greater than or equal to 1. Passing 0, a negative number, or NULL as n will immediately trigger this error and halt query execution.

---

Top 3 Causes

1. Passing Zero or a Negative Integer as n

The most common cause is an off-by-one error during development, especially when reusing array indices (which are zero-based) directly as the nth_value argument.

-- ERROR: n = 0
SELECT
    employee_id,
    salary,
    nth_value(salary, 0) OVER (ORDER BY salary DESC) AS result
FROM employees;
-- ERROR:  argument of nth_value must be greater than zero

-- ERROR: n = -1
SELECT
    employee_id,
    salary,
    nth_value(salary, -1) OVER (ORDER BY salary DESC) AS result
FROM employees;
-- ERROR:  argument of nth_value must be greater than zero

-- FIX: Always ensure n >= 1 using GREATEST()
SELECT
    employee_id,
    salary,
    nth_value(salary, GREATEST(1, :n)) 
        OVER (
            ORDER BY salary DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS result
FROM employees;

2. NULL Value Passed via Dynamic Query or Parameter Binding

When applications bind user input directly to nth_value's second argument without validation, a NULL value triggers error 22016. This is especially common in reporting tools and API-driven dashboards.

-- Dangerous pattern: unvalidated parameter binding
-- If :user_input is NULL, PostgreSQL raises 22016
SELECT
    nth_value(salary, :user_input) OVER (ORDER BY salary DESC)
FROM employees;

-- FIX: Use COALESCE and GREATEST to sanitize the input
SELECT
    nth_value(salary, GREATEST(1, COALESCE(:user_input, 1)))
        OVER (
            ORDER BY salary DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS safe_result
FROM employees;

-- FIX: Use CASE WHEN for explicit NULL handling and return NULL gracefully
SELECT
    employee_id,
    salary,
    CASE
        WHEN :user_input IS NULL OR :user_input < 1 THEN NULL
        ELSE nth_value(salary, :user_input)
             OVER (ORDER BY salary DESC
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    END AS guarded_result
FROM employees;

3. Dynamically Computed n That Results in Zero

When n is derived from an aggregation or calculation (e.g., COUNT(*) / 2), an empty dataset or edge-case data can produce 0, causing the error.

-- Problematic pattern: computed n can be 0 when table is empty
WITH config AS (
    SELECT COUNT(*) / 2 AS target_n  -- Can be 0!
    FROM employees
    WHERE department_id = 99  -- No rows match
)
SELECT
    e.salary,
    nth_value(e.salary, c.target_n)  -- 22016 if target_n = 0
        OVER (ORDER BY e.salary DESC)
FROM employees e
CROSS JOIN config c;

-- FIX: Wrap computed n with GREATEST(1, ...) inside the CTE
WITH config AS (
    SELECT GREATEST(1, COUNT(*) / 2) AS target_n  -- Guaranteed >= 1
    FROM employees
    WHERE department_id = 99
)
SELECT
    e.salary,
    nth_value(e.salary, c.target_n)
        OVER (
            ORDER BY e.salary DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS nth_salary
FROM employees e
CROSS JOIN config c;

---

Quick Fix Solutions

Scenario	Fix
Hardcoded 0 or negative	Replace with a value >= 1
NULL from parameter	Use COALESCE(n, 1)
Computed n may be 0	Wrap with GREATEST(1, computed_n)
Want safe default on bad input	Use CASE WHEN n < 1 THEN NULL ELSE nth_value(...) END

---

Prevention Tips

1. Adopt GREATEST(1, COALESCE(n, 1)) as a team convention

Make it a coding standard that any dynamic value passed to nth_value() is always sanitized with GREATEST(1, COALESCE(n, 1)). This single pattern eliminates both NULL and non-positive integer issues in one shot.

-- Always write it this way when n is dynamic
nth_value(column_name, GREATEST(1, COALESCE(:dynamic_n, 1)))
    OVER (
        PARTITION BY group_col
        ORDER BY sort_col
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )

2. Add boundary-value test cases to your CI/CD pipeline

Include test cases for n = 0, n = -1, and n = NULL in your database integration tests. Use pgTAP or similar frameworks to assert that these inputs are handled gracefully without crashing the query.

-- pgTAP boundary test example
SELECT throws_ok(
    $$ SELECT nth_value(1, 0) OVER () $$,
    '22016',
    'argument of nth_value must be greater than zero',
    'nth_value(col, 0) must raise 22016'
);

---

Related Errors

• 22012 division_by_zero — May co-occur when computing n via division.
• 22003 numeric_value_out_of_range — Triggered if n overflows the INTEGER range.
• 42P20 windowing_error — Related to malformed OVER clause in window functions.

---

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