How to fix 2201G: invalid_argument_for_width_bucket_function in PostgreSQL

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;