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?
answer
- no wraparound, an error
- SQLSTATE 22003
- operand types pick the result type
- cast one operand first
- a type change rewrites everything
basics
~20 sPostgreSQL 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 linesSELECT 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
Recall the integer and bigint ranges, that overflow is an error rather than wraparound, and that :: is PostgreSQL's cast syntax.
Explain how operand types decide the result type, why casting one operand fixes the arithmetic, and how sum() widens its result.
Diagnose stored versus computed overflow from the failing statement, monitor headroom, and plan the column widening without a long ACCESS EXCLUSIVE outage.
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.