PostgreSQL 22016 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22016: Invalid Argument for nth_value Function

PostgreSQL error code 22016 is raised when the NTH_VALUE() window function receives an invalid second argument — specifically when the value is zero, negative, or NULL. The NTH_VALUE(value, n) function requires n to be a positive integer (≥ 1) representing which row's value to return within the window frame. This error is most commonly encountered when n is computed dynamically or sourced from user input without proper validation.

Top 3 Causes

1. Passing Zero or a Negative Integer as N

The most frequent cause is passing 0 or a negative number as the row index.

-- This will raise ERROR 22016
SELECT
    employee_id,
    salary,
    NTH_VALUE(salary, 0) OVER (
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS result
FROM employees;
-- ERROR:  argument of nth_value must be a positive integer

-- Fix: Use GREATEST() to enforce a minimum of 1
SELECT
    employee_id,
    salary,
    NTH_VALUE(salary, GREATEST(dynamic_n, 1)) OVER (
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS result
FROM employees
CROSS JOIN (SELECT 0 AS dynamic_n) AS params;

2. NULL Value Passed as N

When n is derived from a subquery or expression that returns NULL, PostgreSQL treats it as invalid and raises 22016.

-- NULL passed as N → triggers 22016
SELECT
    product_id,
    price,
    NTH_VALUE(price, NULL::integer) OVER (
        ORDER BY price DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS result
FROM products;

-- Fix: Use COALESCE() to provide a safe default
SELECT
    product_id,
    price,
    NTH_VALUE(price, COALESCE(computed_n::integer, 1)) OVER (
        ORDER BY price DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS result
FROM products
CROSS JOIN (SELECT NULL::integer AS computed_n) AS params;

3. Non-Integer or Float Value Passed as N

Passing a float or improperly cast value as n can also cause this error when implicit casting fails or produces an unexpected result.

-- Problematic: float without explicit casting
SELECT
    order_id,
    amount,
    NTH_VALUE(amount, 2.5::integer) OVER (  -- truncation may cause issues
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS result
FROM orders;

-- Fix: Explicitly floor and cast before use
SELECT
    order_id,
    amount,
    NTH_VALUE(amount, FLOOR(2.5)::integer) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS result
FROM orders;

Quick Fix Solutions

Use this defensive pattern whenever n is dynamic:

-- Universal safe pattern
SELECT
    col,
    NTH_VALUE(col, GREATEST(COALESCE(dynamic_n::integer, 1), 1)) OVER (
        ORDER BY col
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS safe_nth
FROM your_table;

Or wrap validation in a reusable PL/pgSQL function:

CREATE OR REPLACE FUNCTION validate_nth_n(input_n INTEGER)
RETURNS INTEGER AS $$
BEGIN
    IF input_n IS NULL OR input_n < 1 THEN
        RAISE EXCEPTION 'nth_value N must be >= 1, got: %', input_n;
    END IF;
    RETURN input_n;
END;
$$ LANGUAGE plpgsql;

Prevention Tips

1. Always validate dynamic N values before passing them to NTH_VALUE(). Use GREATEST(COALESCE(n, 1), 1) as a standard guard in all queries where n is computed at runtime. This single pattern covers both NULL and non-positive integer cases simultaneously.

2. Write boundary-value test cases for any query using NTH_VALUE(). Include test scenarios where n = 0, n = -1, and n = NULL. Integrate these into your CI/CD pipeline using a testing framework like pgTAP to automatically catch 22016 errors before they reach production.

Related Errors

• 22003 numeric_value_out_of_range — Triggered when n exceeds integer bounds.
• 42883 undefined_function — Raised on window function signature mismatches.
• 22012 division_by_zero — Can precede 22016 when n is computed via division.

---

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