Integer Out Of Range Arithmetic Overflow

PostgreSQL's arithmetic operators are typed: integer * integer resolves to the int4mul function, which returns integer. There is no automatic promotion to bigint based on the magnitude of the result, so the multiplication is performed in 32-bit space and overflows when the true product exceeds 2,147,483,647 -- at which point int4mul raises 22003 rather than wrapping around silently (PostgreSQL checks for overflow, unlike C).

Because the failure happens during expression evaluation inside a single statement, the whole UPDATE rolls back.

The fix has two parts that must both hold: the destination column must be wide enough to store the result (bigint), and the arithmetic must be done in a wide type by casting at least one operand to bigint, so the operator chosen is int8mul (which returns bigint) and never overflows for these magnitudes. The fix has two parts that must both hold: the destination column must be wide enough to store the result (bigint), and the arithmetic must be done in a wide type by casting at least one operand to bigint, so the operator chosen is int8mul (which returns bigint) and never overflows for these magnitudes.

Free Tier -- Basic Approach

Before changing anything, size the problem with one count -- how many rows have a product beyond int4 max. Five. Critically, the check casts to bigint first, so measuring the overflow does not itself overflow.

SELECT count(*) AS overflow_rows
FROM order_lines
WHERE unit_price_cents::bigint * quantity > 2147483647;

overflow_rows
---------------
             5
(1 row)

SELECT count(*) AS overflow_rows FROM order_lines WHERE unit_price_cents::bigint * quantity > 2147483647; -- the ::bigint cast makes the comparison safe; without it the diagnostic would raise the same 22003 it is trying to find.

Fix both halves: widen the destination and widen the arithmetic. ALTER line_total to bigint (ALTER TABLE), then recompute casting one operand to bigint so the multiplication runs in int8 and cannot overflow (UPDATE 1000). Every total, including the 4,000,000,000 rows, is now computed and stored correctly.

ALTER TABLE order_lines ALTER COLUMN line_total TYPE bigint;
UPDATE order_lines SET line_total = unit_price_cents::bigint * quantity;

ALTER TABLE
UPDATE 1000

ALTER TABLE order_lines ALTER COLUMN line_total TYPE bigint; then UPDATE order_lines SET line_total = unit_price_cents::bigint * quantity; -- the ::bigint cast selects int8mul (returns bigint), and the widened column stores the result.

Confirm the column is bigint, the large totals are stored as their true values (not wrapped), and a bare int * int that still overflows is rejected -- proof the fix is the bigint arithmetic and storage, not a relaxed type. information_schema reports bigint, max_total is 4,000,000,000, and SELECT 2000000 * 2000 still raises 22003.

SELECT data_type FROM information_schema.columns
WHERE table_name = 'order_lines' AND column_name = 'line_total';

data_type
-----------
 bigint
(1 row)

SELECT max(line_total) AS max_total FROM order_lines;

max_total
------------
 4000000000
(1 row)

SELECT 2000000 * 2000;

ERROR:  integer out of range

Read data_type from information_schema.columns (expect bigint), check max(line_total) equals the true 4,000,000,000, and run SELECT 2000000 * 2000 to confirm a literal int * int overflow is still rejected with SQLSTATE 22003.

📦 Version Matrix (PG 14--18)

The behavior is identical on PostgreSQL 14 through 18: the int * int product raises 'integer out of range', and widening the column plus bigint arithmetic recomputes all rows cleanly (UPDATE 100). The 22003 overflow contract of int4mul is stable across all supported majors.

Frequently Asked Questions

References

• PostgreSQL: Numeric Types (integer / bigint ranges)
• PostgreSQL: Mathematical Functions and Operators
• PostgreSQL: Error Codes (Class 22 -- Data Exception, 22003 numeric_value_out_of_range)
• PostgreSQL: ALTER TABLE -- ALTER COLUMN ... TYPE