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() window function receives an invalid argument — specifically when the bucket count n is zero, negative, or NULL. The NTILE(n) function divides a result set into n roughly equal groups, so it requires n to be a positive integer (≥ 1). This error most commonly surfaces in production environments where bucket counts are computed dynamically rather than hardcoded.

---

Top 3 Causes

1. Passing Zero or a Negative Number as the Bucket Count

The most frequent cause is passing 0 or a negative integer directly or via a computed expression.

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

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

-- Safe fix using GREATEST()
SELECT
    employee_id,
    salary,
    NTILE(GREATEST(1, :bucket_count)) OVER (ORDER BY salary DESC) AS bucket
FROM employees;

2. Passing a NULL Value as the Argument

When the bucket count is fetched from a configuration table or passed as a variable, a missing record or uninitialized variable can silently produce NULL, triggering 22014.

-- Triggers 22014: NULL argument from a config table
WITH config AS (
    SELECT config_value::INTEGER AS bucket_count
    FROM app_config
    WHERE config_key = 'report_buckets'  -- row might not exist → NULL
)
SELECT
    employee_id,
    salary,
    NTILE((SELECT bucket_count FROM config)) OVER (ORDER BY salary DESC) AS bucket
FROM employees;
-- ERROR:  argument of ntile must be greater than zero

-- Safe fix using COALESCE()
WITH config AS (
    SELECT COALESCE(
        (SELECT config_value::INTEGER FROM app_config WHERE config_key = 'report_buckets'),
        4  -- sensible default
    ) AS bucket_count
)
SELECT
    employee_id,
    salary,
    NTILE((SELECT bucket_count FROM config)) OVER (ORDER BY salary DESC) AS bucket
FROM employees;

3. Dynamic Bucket Count That Can Become Zero at Runtime

A common pattern computes the number of buckets from row counts or business logic. If the filtered dataset is smaller than expected, the computed value can collapse to zero.

-- Risky: dynamically computed bucket count can become 0
WITH stats AS (
    SELECT COUNT(*)::INTEGER / 10 AS bucket_count  -- returns 0 when count < 10
    FROM employees
    WHERE department_id = 99  -- sparse department
)
SELECT
    employee_id,
    salary,
    NTILE((SELECT bucket_count FROM stats)) OVER (ORDER BY salary DESC) AS bucket
FROM employees
WHERE department_id = 99;
-- ERROR:  argument of ntile must be greater than zero

-- Safe fix: wrap with GREATEST()
WITH stats AS (
    SELECT GREATEST(1, COUNT(*)::INTEGER / 10) AS bucket_count
    FROM employees
    WHERE department_id = 99
)
SELECT
    employee_id,
    salary,
    NTILE((SELECT bucket_count FROM stats)) OVER (ORDER BY salary DESC) AS bucket
FROM employees
WHERE department_id = 99;

---

Quick Fix Solutions

Use the GREATEST(1, COALESCE(value, default)) pattern as a universal safety net whenever NTILE receives a dynamic argument:

-- Universal safe pattern
SELECT
    employee_id,
    salary,
    NTILE(GREATEST(1, COALESCE(dynamic_n, 4))) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS bucket
FROM employees
CROSS JOIN (SELECT :n AS dynamic_n) params;

For PL/pgSQL procedures, validate the argument explicitly before executing the query:

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

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

---

Prevention Tips

1. Always use GREATEST(1, COALESCE(...)) for dynamic NTILE arguments. Treat this as a non-negotiable coding standard in your team. Add a linting rule or code review checklist item to catch bare dynamic arguments passed to NTILE.

2. Add boundary-value test cases to your CI/CD pipeline. Test NTILE logic with inputs of 0, -1, NULL, and very large integers before every deployment. Use pgTAP for structured database-level unit testing to catch these issues automatically before they reach production.

---

Related Errors

Code	Name	Notes
22003	numeric_value_out_of_range	Triggered when a numeric argument exceeds its type's range
22004	null_value_not_allowed	NULL passed where a non-null value is required
22023	invalid_parameter_value	General invalid parameter in function calls
42883	undefined_function	Wrong argument type passed to a function

---

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