numeric value out of range

This error occurs when a number is too large or too small for the column's data type.

The numeric value out of range error means the number exceeds the data type's limits.

Understanding the Error

ERROR: integer out of range
ERROR: numeric field overflow

The value is larger (or smaller) than what the data type can store.

Common Causes

1. Integer Overflow

SQL

-- INTEGER max is 2,147,483,647
INSERT INTO counters (value) VALUES (3000000000);  -- Error!

2. Precision Overflow

SQL

-- NUMERIC(10,2) means 10 total digits, 2 after decimal
INSERT INTO prices (amount) VALUES (123456789.99);  -- Too many digits!

3. SMALLINT Limit

SQL

-- SMALLINT max is 32,767
UPDATE stats SET count = 50000 WHERE id = 1;  -- Error if column is SMALLINT

How to Fix It

Solution 1: Use Larger Data Type

SQL

-- Change from INTEGER to BIGINT
ALTER TABLE counters ALTER COLUMN value TYPE BIGINT;

-- BIGINT max is 9,223,372,036,854,775,807

Solution 2: Increase NUMERIC Precision

SQL

-- Increase total digits
ALTER TABLE prices ALTER COLUMN amount TYPE NUMERIC(15, 2);

Solution 3: Validate Before Insert

JAVASCRIPT

const MAX_INT = 2147483647;

function validateCounter(value) {
  if (value > MAX_INT) {
    throw new Error('Value exceeds maximum allowed');
  }
  return value;
}

Solution 4: Use TEXT for Very Large Numbers

SQL

-- Store as text if you don't need arithmetic
ALTER TABLE data ADD COLUMN big_number TEXT;

-- Or use NUMERIC without precision limit
ALTER TABLE data ADD COLUMN big_number NUMERIC;

PostgreSQL Integer Types

| Type | Storage | Min | Max | |------|---------|-----|-----| | SMALLINT | 2 bytes | -32,768 | 32,767 | | INTEGER | 4 bytes | -2,147,483,648 | 2,147,483,647 | | BIGINT | 8 bytes | -9.2×10¹⁸ | 9.2×10¹⁸ | | SERIAL | 4 bytes | 1 | 2,147,483,647 | | BIGSERIAL | 8 bytes | 1 | 9.2×10¹⁸ |

NUMERIC Precision

SQL

-- NUMERIC(precision, scale)
-- precision = total digits
-- scale = digits after decimal

NUMERIC(5, 2)  -- -999.99 to 999.99
NUMERIC(10, 4) -- -999999.9999 to 999999.9999
NUMERIC        -- Unlimited precision (but slower)

Best Practices

Choose appropriate types during table design
Use BIGINT for IDs that might grow large
Use NUMERIC for financial calculations
Validate input before database operations
Plan for growth in counter/sequence columns