skip to content

A PostgreSQL fare-ledger insert fails with 'integer out of range' — what overflowed, how do :: casts fix the arithmetic, and what does moving to bigint cost?

level: seniorimportance: should knowfreq 42%

answer

  1. no wraparound, an error
  2. SQLSTATE 22003
  3. operand types pick the result type
  4. cast one operand first
  5. a type change rewrites everything

basics

~20 s

PostgreSQL raised SQLSTATE 22003 because a value or an integer-times-integer result passed 2,147,483,647; integers never wrap. Cast an operand with ::bigint so the arithmetic runs in 64 bits; changing the column type rewrites the table and its indexes.

solid answer

~40 s

`integer` is 4 bytes, −2,147,483,648 to 2,147,483,647, and PostgreSQL never wraps: an out-of-range assignment or result raises `integer out of range` with SQLSTATE `22003` (`numeric_value_out_of_range`). Two different things hit it. Either the column itself has run out — a running total in cents, a counter — or an expression overflowed even though its destination is wide, because `integer * integer` is computed as `integer`. `::` is PostgreSQL's cast syntax, equivalent to `CAST(... AS ...)`; casting one operand, `price_cents::bigint * seats`, makes the whole product `bigint`. Aggregates are already safe: `sum(integer)` returns `bigint`. Fixing the column with `ALTER COLUMN ... TYPE bigint` rewrites the table and every index under an `ACCESS EXCLUSIVE` lock, so on a large, busy table teams add a `bigint` column, backfill it in batches and swap.

code

sql · 12 lines
sql
SELECT 50000 * 50000;                  -- ERROR:  integer out of range
SELECT 50000::bigint * 50000;          -- 2500000000
SELECT CAST(50000 AS bigint) * 50000;  -- 2500000000, SQL-standard spelling

SELECT 7 / 2, 7 / 2.0;                 -- 3 | 3.5000000000000000
SELECT 1.5::integer;                   -- 2  (numeric to integer rounds)
SELECT '1.5'::integer;                 -- ERROR:  invalid input syntax for type integer: "1.5"

-- headroom left in an integer column of the fare ledger
SELECT max(total_cents)              AS current_max,
       2147483647 - max(total_cents) AS headroom
FROM booking;

go deeper

for a junior

Recall the integer and bigint ranges, that overflow is an error rather than wraparound, and that :: is PostgreSQL's cast syntax.

for a middle

Explain how operand types decide the result type, why casting one operand fixes the arithmetic, and how sum() widens its result.

for a senior

Diagnose stored versus computed overflow from the failing statement, monitor headroom, and plan the column widening without a long ACCESS EXCLUSIVE outage.

for a principal

Set a default of bigint for counters and money in minor units, weighing four extra bytes per value against a forced migration years later.

## The integer types and their limits | Type | Size | Range | |---|---|---| | `smallint` | 2 bytes | −32,768 to 32,767 | | `integer` | 4 bytes | −2,147,483,648 to 2,147,483,647 | | `bigint` | 8 bytes | −9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | | `numeric` | variable | exact, effectively unbounded | PostgreSQL **checks every integer operation**. There is no silent wraparound into negative numbers: a result that does not fit raises `integer out of range` (or `bigint out of range`), SQLSTATE **`22003`**, `numeric_value_out_of_range`. The failing statement is rolled back, which for a ledger is the good outcome — but it is still an outage for writes. ## How PostgreSQL types an expression The surprise is usually that the column was not the problem. The **operand types** decide the result type, not the destination: - an integer literal is typed `integer` if it fits in 32 bits, otherwise `bigint`, otherwise `numeric`; a literal with a decimal point is `numeric`; - `integer * integer` is computed as `integer`, so `price_cents * seats` can overflow while being inserted into a `bigint` column; - `/` between integers **truncates toward zero**: `7 / 2` is 3, while `7 / 2.0` is a `numeric` 3.5000000000000000; - aggregates widen for you: `sum(integer)` returns `bigint`, `sum(bigint)` returns `numeric`, and `count(*)` returns `bigint`. ## The :: cast PostgreSQL accepts two equivalent cast syntaxes: SQL-standard `CAST(expr AS type)` and the historical PostgreSQL form `expr::type`. Facts that come up: - `50000::bigint * 50000` succeeds because one `bigint` operand makes the multiplication `bigint`; - casting `numeric` to `integer` **rounds**: `1.5::integer` is 2; - casting a *string* to `integer` parses it, and `'1.5'::integer` fails with `invalid input syntax for type integer`, SQLSTATE `22P02`; - `::` binds tightly, so cast the operand, not the finished product: `(a * b)::bigint` has already overflowed. ## Diagnosing which overflow you hit 1. Read the failing statement from the error log, not just the message: is the overflowing value being *stored*, or *computed*? 2. If computed, find the `integer * integer` (or `+`) and cast one operand with `::bigint` or `::numeric`. 3. If stored, compare `max(column)` with 2,147,483,647 to see how much headroom is left, and alert well before it is gone. 4. If the column is a key fed by a sequence or identity, the sequence's own limits and type are the keys topic's business; the column-width problem is the same. ## Paying for bigint - `ALTER TABLE ... ALTER COLUMN ... TYPE bigint` is a real type change: it **rewrites the table and all of its indexes**, holding an `ACCESS EXCLUSIVE` lock that blocks reads and writes for the duration. - Foreign key columns that reference the widened column should be widened as well; an `integer` referencing column keeps the old ceiling, and each one is another rewrite. - The new column costs 4 more bytes per value in the heap and in every index containing it. - On a large, busy table the online path is add a nullable `bigint` column, keep it in sync, backfill in batches, then swap names in a short transaction. The lock behaviour of each step belongs to the online schema change topic, and the method itself to expand-contract. ## Other places the same limit bites - `UPDATE ... SET counter = counter + 1` fails on the row that reaches the limit, so one hot counter can block one customer while the rest of the table works. - Negating an `integer` that holds the minimum value, −2,147,483,648, overflows too, because there is no positive `integer` counterpart. - Unix epoch seconds stored in an `integer` run out at 2038-01-19 03:14:07 UTC; a fare service storing validity dates that way fails as soon as anyone stores a date past that moment, years before it arrives. `timestamptz` or `bigint` has no such ceiling in practice. - Narrow columns chosen to save space, such as `smallint` for seat counts on a large aircraft or a loyalty-point balance, overflow at 32,767 with the same SQLSTATE. The cheapest fix is the one made at design time: `bigint` for anything that counts events or money in minor units and might plausibly pass two billion.

  • Why does sum(seats) over an integer column not overflow when seats * price_cents does?
    `sum(integer)` is declared to return `bigint` and accumulates in that wider type, and `sum(bigint)` returns `numeric`. Multiplication of two `integer` values is declared to return `integer`, so the product is checked against the 32-bit range before it ever reaches a wider column. Casting one operand with `::bigint` changes which operator is chosen.
  • On PostgreSQL, why is ALTER COLUMN ... TYPE bigint on a 500 GB table rarely run directly?
    It rewrites the whole table and rebuilds every index while holding `ACCESS EXCLUSIVE`, so the table is unreadable and unwritable for the duration, and referencing foreign key columns need the same treatment or they keep the old ceiling. Teams add a new `bigint` column, keep it in sync, backfill in batches and swap in a brief transaction; the locking details belong to the online schema change topic.

  • PostgreSQL integers wrap around to negative values on overflow.
  • Inserting into a bigint column prevents an integer * integer overflow.
  • sum() over an integer column overflows at about 2.1 billion.
  • ALTER COLUMN TYPE bigint is a quick catalog-only change.
  • '1.5'::integer rounds to 2 like 1.5::integer does.