📋

Description

This PostgreSQL error indicates that an attempt was made to store a numeric value that exceeds the defined range or precision of its target data type. It typically occurs when a number is too large or too small to fit within the column's capacity, leading to a data exception.

💬

Error Message

numeric value out of range

🔍

Known Causes

3 known causes

⚠️

Insufficient Column Data Type

The column's assigned numeric data type (e.g., SMALLINT, INT, NUMERIC(p,s)) is too narrow to accommodate the incoming value, causing an overflow or underflow.

⚠️

Invalid Input Data

An application or user attempted to insert or update a value that inherently exceeds the maximum or minimum representable value for the target numeric column.

⚠️

Arithmetic Operation Overflow

A mathematical calculation within the database produced a result that is larger or smaller than what the destination numeric column or expression can store.

🛠️

Solutions

4 solutions available

1. Use Larger Numeric Type easy

Change to type that fits your values

1

Understand numeric type ranges

-- SMALLINT: -32768 to 32767
-- INTEGER: -2147483648 to 2147483647
-- BIGINT: -9223372036854775808 to 9223372036854775807
-- NUMERIC: Unlimited precision

2

Change to larger type

ALTER TABLE transactions ALTER COLUMN amount TYPE BIGINT;

3

For decimal values

ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(15, 2);

2. Fix NUMERIC Precision easy

Increase precision for NUMERIC columns

1

Check current precision

SELECT numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_name = 'products' AND column_name = 'price';

2

Increase precision

-- NUMERIC(10,2) can hold up to 99999999.99
-- Change to NUMERIC(15,2) for larger values
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(15, 2);

3. Handle Division by Zero easy

Prevent infinite results

1

Use NULLIF to prevent division by zero

SELECT total / NULLIF(count, 0) AS average FROM stats;

2

Use CASE for more control

SELECT 
  CASE WHEN count = 0 THEN 0
       ELSE total / count
  END AS average
FROM stats;

4. Validate Input Range easy

Check values before inserting

1

Add CHECK constraint

ALTER TABLE orders ADD CONSTRAINT check_quantity
  CHECK (quantity > 0 AND quantity < 1000000);

2

Validate in application

def validate_amount(amount):
    MAX_INT = 2147483647
    if amount > MAX_INT:
        raise ValueError('Amount exceeds maximum')
    return amount

🔗

Related Errors

5 related errors

PostgreSQL Error 22003: Numeric Value Out of Range