How to Fix PostgreSQL Error: Integer Out of Range

The "Integer out of range" error occurs when trying to store or calculate a value that exceeds the limits of the integer data type being used. PostgreSQL has different integer types with different maximum values.

Impact

This error prevents data insertion or calculations from completing, causing application failures when dealing with large numbers or accumulating values.

Common Causes

1. Using INTEGER when BIGINT is needed
2. Auto-increment sequences exceeding INTEGER limits
3. Calculations producing values larger than data type
4. Multiplication or aggregation of large values
5. Converting from larger to smaller integer types

Troubleshooting and Resolution Steps

1. Understanding PostgreSQL integer types:

   -- SMALLINT: -32,768 to 32,767
   -- INTEGER: -2,147,483,648 to 2,147,483,647
   -- BIGINT: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
   
   -- Check current column type
   \d table_name
   

2. Change column type to BIGINT:

   -- Change INTEGER to BIGINT
   ALTER TABLE orders ALTER COLUMN id TYPE BIGINT;
   ALTER TABLE orders ALTER COLUMN total_amount TYPE BIGINT;
   
   -- Change SERIAL to BIGSERIAL
   -- First, change column type
   ALTER TABLE users ALTER COLUMN id TYPE BIGINT;
   -- Then update sequence
   ALTER SEQUENCE users_id_seq AS BIGINT;
   

3. Handle calculations properly:

   -- WRONG: Integer multiplication overflow
   SELECT quantity * price FROM orders;  -- May overflow
   
   -- CORRECT: Cast to BIGINT or NUMERIC
   SELECT quantity::BIGINT * price FROM orders;
   SELECT quantity * price::NUMERIC FROM orders;
   

4. Use NUMERIC for very large numbers or decimals:

   -- For financial or very large values
   ALTER TABLE transactions ALTER COLUMN amount TYPE NUMERIC(20,2);
   
   -- NUMERIC can handle arbitrary precision
   CREATE TABLE large_numbers (
       id BIGSERIAL PRIMARY KEY,
       value NUMERIC(30,0)  -- Up to 30 digits
   );
   

5. Reset sequence for BIGSERIAL:

   -- Convert SERIAL to BIGSERIAL safely
   BEGIN;
   ALTER TABLE mytable ALTER COLUMN id TYPE BIGINT;
   ALTER SEQUENCE mytable_id_seq AS BIGINT MAXVALUE 9223372036854775807;
   COMMIT;
   

Additional Information

• Use BIGINT for IDs and counters in production
• BIGSERIAL for auto-increment primary keys
• NUMERIC for financial calculations
• Cast intermediate calculations to prevent overflow
• Monitor sequence values approaching limits

Frequently Asked Questions

Q: When should I use BIGINT instead of INTEGER?
A: Use BIGINT for primary keys, large counters, timestamps, or any value that might exceed 2 billion.

Q: What's the performance difference between INTEGER and BIGINT?
A: Minimal. BIGINT uses 8 bytes vs 4 bytes for INTEGER, but the performance impact is negligible in most cases.

Q: Can I change INTEGER to BIGINT without downtime?
A: In recent PostgreSQL versions (11+), ALTER COLUMN TYPE to BIGINT is fast and doesn't rewrite the table if no data transformation is needed.

Q: How do I know if my sequence is approaching the limit?
A: Query: SELECT last_value FROM sequence_name; and compare to 2,147,483,647 (INTEGER max).