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.