PostgreSQL 2201G Error: Causes and Solutions Complete Guide

PostgreSQL Error 2201G: Invalid Argument for Width Bucket Function

PostgreSQL error code 2201G is raised when the width_bucket() function receives an argument that violates its mathematical preconditions. This function distributes a value into equally-sized buckets within a specified range, but it requires strict constraints on its inputs to operate correctly. When those constraints are broken — such as passing zero buckets, identical bounds, or non-finite values — PostgreSQL immediately throws this error.

---

Top 3 Causes

1. Bucket Count Is Zero or Negative

The count parameter of width_bucket(operand, low, high, count) must be a positive integer (≥ 1). Passing 0 or a negative number makes it impossible to divide the range into valid buckets.

-- This will fail with error 2201G
SELECT width_bucket(75, 0, 100, 0);
-- ERROR: count must be greater than zero

-- Fix: Use GREATEST() to guarantee a minimum of 1
SELECT width_bucket(75, 0, 100, GREATEST(1, :dynamic_count));

-- Fix: Guard with CASE
SELECT
    score,
    CASE
        WHEN bucket_count <= 0 THEN NULL
        ELSE width_bucket(score, 0, 100, bucket_count)
    END AS bucket
FROM exam_results;

2. Lower Bound Equals Upper Bound

When low = high, the bucket width is zero, making division mathematically undefined. This commonly occurs when processing datasets where all values are identical.

-- This will fail with error 2201G
SELECT width_bucket(50, 100, 100, 10);
-- ERROR: lower bound cannot equal upper bound

-- Fix: Use CASE to detect equal bounds
WITH stats AS (
    SELECT MIN(price) AS lo, MAX(price) AS hi
    FROM products
)
SELECT
    p.product_id,
    p.price,
    CASE
        WHEN s.lo = s.hi THEN 1  -- assign everything to bucket 1
        ELSE width_bucket(p.price, s.lo, s.hi, 10)
    END AS price_bucket
FROM products p
CROSS JOIN stats s;

3. NaN or Infinity Values in Input

Passing NaN, Infinity, or -Infinity to any of the numeric parameters (operand, low, or high) triggers this error. These values frequently sneak in from external data sources or failed arithmetic operations.

-- These will fail with error 2201G
SELECT width_bucket('NaN'::float, 0, 100, 10);
SELECT width_bucket(1.0/0, 0, 100, 10);

-- Fix: Use isfinite() to filter out non-finite values
SELECT
    sensor_id,
    reading_value,
    CASE
        WHEN reading_value IS NULL THEN NULL
        WHEN NOT isfinite(reading_value) THEN NULL
        ELSE width_bucket(reading_value, 0.0, 100.0, 10)
    END AS bucket
FROM sensor_readings;

---

Quick Fix: Create a Safe Wrapper Function

The most reliable fix is to encapsulate all guard logic into a reusable function.

CREATE OR REPLACE FUNCTION safe_width_bucket(
    operand double precision,
    low     double precision,
    high    double precision,
    count   integer
)
RETURNS integer
LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
    IF operand IS NULL OR low IS NULL OR high IS NULL OR count IS NULL THEN
        RETURN NULL;
    END IF;
    IF NOT isfinite(operand) OR NOT isfinite(low) OR NOT isfinite(high) THEN
        RETURN NULL;
    END IF;
    IF count <= 0 THEN
        RETURN NULL;
    END IF;
    IF low = high THEN
        RETURN 1;
    END IF;
    RETURN width_bucket(operand, low, high, count);
END;
$$;

-- Usage
SELECT safe_width_bucket(score, 0, 100, 10) AS bucket
FROM exam_results;

---

Prevention Tips

1. Add CHECK constraints at the table level to block non-finite values from being stored in the first place:

ALTER TABLE sensor_readings
ADD CONSTRAINT chk_finite_reading
CHECK (reading_value IS NULL OR isfinite(reading_value));

2. Run data quality checks before executing analytical queries that rely on width_bucket():

-- Pre-flight data quality check
SELECT
    COUNT(*) FILTER (WHERE NOT isfinite(measurement)) AS bad_values,
    MIN(measurement)  AS min_val,
    MAX(measurement)  AS max_val
FROM raw_measurements
HAVING COUNT(*) FILTER (WHERE NOT isfinite(measurement)) > 0;

By combining defensive wrapper functions with upstream data validation, you can virtually eliminate 2201G errors from your production environment.

---

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