PostgreSQL 22014 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22014: Invalid Argument for NTILE Function

PostgreSQL error code 22014 is raised when the NTILE(n) window function receives an invalid argument — specifically when n is NULL, 0, or a negative integer. The NTILE(n) function divides a result set into n ranked buckets, so any value that doesn't represent a positive integer makes the operation logically impossible. This error is most commonly encountered in dynamic queries, parameterized functions, or when user-supplied values are passed directly into the function without validation.

---

Top 3 Causes

1. Passing NULL as the NTILE Argument

The most frequent cause is a NULL value reaching the NTILE() function. This often happens when a subquery or application parameter unexpectedly returns no value.

-- Triggers ERROR 22014
SELECT
    employee_id,
    salary,
    NTILE(NULL) OVER (ORDER BY salary DESC) AS bucket
FROM employees;
-- ERROR:  argument of ntile must be greater than zero

-- A realistic scenario where NULL sneaks in
WITH config AS (
    SELECT NULL::INTEGER AS n  -- value missing from config table
)
SELECT
    employee_id,
    NTILE((SELECT n FROM config)) OVER (ORDER BY salary DESC) AS bucket
FROM employees;

2. Passing Zero or a Negative Integer

Passing 0 or any negative number is equally invalid. This often stems from miscalculated business logic or a misconfigured report parameter.

-- Triggers ERROR 22014 with zero
SELECT
    product_id,
    revenue,
    NTILE(0) OVER (ORDER BY revenue DESC) AS tier
FROM sales;
-- ERROR:  argument of ntile must be greater than zero

-- Triggers ERROR 22014 with a negative value
SELECT
    product_id,
    revenue,
    NTILE(-3) OVER (ORDER BY revenue DESC) AS tier
FROM sales;
-- ERROR:  argument of ntile must be greater than zero

3. Unvalidated Parameters in PL/pgSQL Functions or Dynamic SQL

When wrapping NTILE() inside a PL/pgSQL function or a dynamically built query, failing to validate the input before execution is a common pitfall in production environments.

-- Dangerous: no input validation
CREATE OR REPLACE FUNCTION rank_employees(p_buckets INTEGER)
RETURNS TABLE(emp_id INT, salary NUMERIC, bucket INT) AS $$
BEGIN
    RETURN QUERY
    SELECT
        e.employee_id,
        e.salary,
        NTILE(p_buckets) OVER (ORDER BY e.salary DESC)::INT
    FROM employees e;
    -- Explodes if p_buckets is NULL, 0, or negative!
END;
$$ LANGUAGE plpgsql;

SELECT * FROM rank_employees(0);   -- ERROR 22014
SELECT * FROM rank_employees(NULL); -- ERROR 22014

---

Quick Fix Solutions

Fix 1: Use COALESCE + GREATEST for Inline Defense

-- Safe pattern: guarantees n >= 1 at all times
SELECT
    employee_id,
    salary,
    NTILE(GREATEST(COALESCE($1, 4), 1)) OVER (ORDER BY salary DESC) AS bucket
FROM employees;

Fix 2: Add Input Validation Inside PL/pgSQL

CREATE OR REPLACE FUNCTION rank_employees(p_buckets INTEGER)
RETURNS TABLE(emp_id INT, salary NUMERIC, bucket INT) AS $$
BEGIN
    IF p_buckets IS NULL OR p_buckets <= 0 THEN
        RAISE EXCEPTION 'p_buckets must be a positive integer, received: %', p_buckets
            USING ERRCODE = '22014';
    END IF;

    RETURN QUERY
    SELECT
        e.employee_id,
        e.salary,
        NTILE(p_buckets) OVER (ORDER BY e.salary DESC)::INT
    FROM employees e;
END;
$$ LANGUAGE plpgsql;

-- Works correctly
SELECT * FROM rank_employees(5);

-- Raises a clear, descriptive error
SELECT * FROM rank_employees(0);

Fix 3: Create a Safe NTILE Wrapper Function

-- Reusable utility to sanitize NTILE input across your codebase
CREATE OR REPLACE FUNCTION safe_ntile_arg(p_n INTEGER, p_default INTEGER DEFAULT 4)
RETURNS INTEGER AS $$
    SELECT GREATEST(COALESCE(p_n, p_default), 1);
$$ LANGUAGE sql IMMUTABLE;

-- Usage
SELECT
    employee_id,
    salary,
    NTILE(safe_ntile_arg($1)) OVER (ORDER BY salary DESC) AS bucket
FROM employees;

---

Prevention Tips

1. Enforce constraints at the data layer.
If bucket counts are stored in a configuration table, add a CHECK constraint to prevent invalid values from ever being saved.

CREATE TABLE report_settings (
    id           SERIAL PRIMARY KEY,
    report_name  TEXT NOT NULL,
    bucket_count INTEGER NOT NULL CHECK (bucket_count >= 1)
);

2. Always test boundary values.
Include NULL, 0, -1, and 1 in your test suite for any function that uses NTILE(). Catching these at the CI/CD stage is far cheaper than debugging a production incident.

-- Quick boundary check
SELECT NTILE(GREATEST(COALESCE(val, 1), 1)) OVER (ORDER BY id)
FROM generate_series(1, 10) AS t(id)
CROSS JOIN (VALUES (NULL), (0), (-1), (1), (5)) AS v(val);

---

Related Errors

Error Code	Name	Relation
22012	division_by_zero	Similar arithmetic window function error
22003	numeric_value_out_of_range	NTILE argument exceeds INTEGER bounds
42883	undefined_function	Wrong argument type passed to NTILE

---

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