How to fix 2201G: invalid_argument_for_width_bucket_function in PostgreSQL

What this error means

The width_bucket() function in PostgreSQL is used to assign values to histogram buckets. It takes four arguments: the value to bucket, the lower bound, the upper bound, and the number of buckets. The error "2201G: invalid_argument_for_width_bucket_function" occurs when these arguments violate mathematical constraints. The function creates equal-width buckets between the lower and upper bounds. For example, width_bucket(15, 0, 100, 10) would return bucket 2 (since 15 falls in the second 10-unit bucket). The error typically happens when: 1. The lower bound equals or exceeds the upper bound 2. The number of buckets is zero or negative 3. The value is NaN (not a number) or infinite 4. The bounds themselves are invalid (NaN or infinite) This is a data exception error (SQLSTATE class 22) that prevents the function from performing meaningful calculations.

Typical symptoms

• Query fails with SQLSTATE 2201G error
• Error appears when using width_bucket() function in SELECT statements
• Aggregate queries or window functions using width_bucket() fail
• Data analysis or reporting queries that create histograms fail
• Error message includes 'invalid_argument_for_width_bucket_function'

Common causes

1. Lower bound >= upper bound in width_bucket() arguments (most common)
2. Number of buckets is zero or negative
3. Passing NaN (not a number) values as arguments
4. Passing infinite values (Infinity or -Infinity) as bounds
5. Using non-numeric data types that can't be implicitly cast
6. NULL values in arguments without proper handling
7. Dynamic bounds calculation that produces invalid ranges

How to fix "2201G: invalid_argument_for_width_bucket_function"

width_bucket(operand numeric, low numeric, high numeric, count integer)

-- This will fail: lower bound >= upper bound
SELECT width_bucket(50, 100, 0, 10);

-- This will fail: zero buckets
SELECT width_bucket(50, 0, 100, 0);

-- Check bounds before using width_bucket
SELECT
  CASE
    WHEN low >= high THEN NULL  -- Handle invalid bounds
    WHEN count <= 0 THEN NULL   -- Handle invalid bucket count
    ELSE width_bucket(value, low, high, count)
  END as bucket
FROM your_table;

-- Or use a WHERE clause to filter invalid data
SELECT width_bucket(value, low, high, count)
FROM your_table
WHERE low < high AND count > 0;

WITH validated_data AS (
  SELECT
    value,
    LEAST(min_bound, max_bound) as low,
    GREATEST(min_bound, max_bound) as high,
    CASE WHEN bucket_count <= 0 THEN 1 ELSE bucket_count END as count
  FROM source_data
)
SELECT width_bucket(value, low, high, count)
FROM validated_data;

-- Use COALESCE to provide defaults for NULL values
SELECT width_bucket(
  COALESCE(value, 0),
  COALESCE(low, 0),
  COALESCE(high, 100),
  COALESCE(count, 10)
)
FROM your_table;

-- Or filter out NULL rows
SELECT width_bucket(value, low, high, count)
FROM your_table
WHERE value IS NOT NULL
  AND low IS NOT NULL
  AND high IS NOT NULL
  AND count IS NOT NULL;

SELECT width_bucket(value, low, high, count)
FROM your_table
WHERE
  value = value  -- NaN != NaN, so this filters NaN values
  AND low = low
  AND high = high
  AND low < high
  AND count > 0;

-- Test with simple values first
SELECT width_bucket(50, 0, 100, 10);  -- Should return 5

-- Then test with your actual bounds
SELECT
  low,
  high,
  low < high as bounds_valid,
  count,
  count > 0 as count_valid
FROM your_table
WHERE your_conditions;

-- If bounds might be reversed, try this:
SELECT width_bucket(
  value,
  LEAST(low, high),  -- Ensure low is smaller
  GREATEST(low, high), -- Ensure high is larger
  ABS(count)          -- Ensure positive count
)
FROM your_table;

CREATE OR REPLACE FUNCTION safe_width_bucket(
  operand numeric,
  low numeric,
  high numeric,
  count integer
) RETURNS integer AS $$
BEGIN
  IF low >= high OR count <= 0 THEN
    RETURN NULL;
  END IF;
  RETURN width_bucket(operand, low, high, count);
END;
$$ LANGUAGE plpgsql;

-- Instead of width_bucket(), use CASE for fixed buckets
SELECT
  CASE
    WHEN value < 0 THEN 0
    WHEN value < 10 THEN 1
    WHEN value < 20 THEN 2
    -- ... more buckets
    ELSE 10
  END as bucket
FROM your_table;

-- Create 10 equal-sized buckets based on data distribution
SELECT
  value,
  ntile(10) OVER (ORDER BY value) as percentile_bucket
FROM your_table;

Advanced notes

-- Use percent_rank() for dynamic ranges
SELECT
  value,
  width_bucket(
    percent_rank() OVER (ORDER BY value) * 100,
    0, 100, 10
  ) as dynamic_bucket
FROM your_table;

SELECT
  width_bucket(salary, 30000, 150000, 10) as salary_bucket,
  COUNT(*) as employee_count
FROM employees
GROUP BY 1
ORDER BY 1;

SELECT
  width_bucket(
    EXTRACT(EPOCH FROM event_time - '2024-01-01'),
    0, 31536000, 52  -- 52 weeks in a year
  ) as week_number,
  COUNT(*) as events
FROM events
GROUP BY 1;

Frequently asked questions

What does "2201G" mean?

The width_bucket() function in PostgreSQL is used to assign values to histogram buckets. It takes four arguments: the value to bucket, the lower bound, the upper bound, and the number of buckets. The error "2201G: invalid_argument_for_width_bucket_function" occurs when these arguments violate mathematical constraints.

How do I fix "2201G"?

This PostgreSQL error occurs when invalid arguments are passed to the width_bucket() function, which is used for histogram calculations. The function requires numeric arguments with proper bounds and bucket counts that follow mathematical constraints.

What causes "2201G"?

Lower bound >= upper bound in width_bucket() arguments (most common) Number of buckets is zero or negative Passing NaN (not a number) values as arguments

Is the "2201G" error serious?

It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Related errors

• 2201W: invalid_row_count_in_limit_clause

  How to fix "Invalid row count in LIMIT clause" in PostgreSQL

  MEDIUM

• 2201F: invalid_argument_for_power_function

  Invalid argument for power function (2201F)

  MEDIUM

• 2201X: invalid_row_count_in_result_offset_clause

  How to fix '2201X: invalid_row_count_in_result_offset_clause' in PostgreSQL

  LOW

• 2201E: invalid_argument_for_logarithm

  Invalid argument for logarithm (2201E)

  MEDIUM

• Invalid password

  How to fix "Invalid password" in PostgreSQL

  HIGH

• FATAL: no pg_hba.conf entry for host

  How to fix 'FATAL: no pg_hba.conf entry for host' in PostgreSQL

  HIGH

Official resources & further reading

• 📚PostgreSQL Official Documentation
• 📖
  postgresql.org - functions math(Official Docs)
• 📖
  postgresql.org - functions window(Official Docs)
• 💬
  stackoverflow.com - postgresql+width bucket(Stack Overflow)