integer out of range

What it means

Each integer type has a fixed range:

A value outside the range of its column, or a calculation whose result doesn’t fit the type it’s computed in, fails the statement. The message names the type (integer out of range, smallint out of range, bigint out of range) but not the column or the value.

Arithmetic is the surprising one. 50000 * 50000 is two integers, so PostgreSQL computes it as an integer, and the result (2.5 billion) doesn’t fit, even if you were going to store it in a bigint. A quoted value gets a slightly different message that shows it: value "3000000000" is out of range for type integer.

Common causes

1. An id column that ran out: an integer primary key fed by a bigint sequence (common in tables created before PostgreSQL 10, or by tools that create sequences separately) passes 2,147,483,647.
2. Big real-world numbers: file sizes in bytes, money in the smallest unit, Unix timestamps in milliseconds, phone numbers stored as numbers.
3. Integer arithmetic that overflows before the result is stored: price * quantity, a product of counts, count * 3000000000.
4. Casting a large numeric or double precision to integer: 3.7e9::integer, power(10, 10)::integer.

How to fix it

Compute in bigint or numeric

Cast one side before the arithmetic, so the whole calculation uses the wider type:

SELECT 50000::bigint * 50000;               -- 2500000000
SELECT sum(quantity::bigint * unit_cents) FROM order_lines;

sum() and count() already return bigint for integer input, so they don’t overflow at 2.1 billion.

Change the column to bigint

ALTER TABLE tickets ALTER COLUMN id TYPE bigint;
ALTER SEQUENCE tickets_id_seq AS bigint;

The first statement rewrites the table and its indexes under an ACCESS EXCLUSIVE lock, which can take a long time on a big table; plan it, or use the add-a-column-and-backfill approach in changing a column’s type. Change the foreign keys that point at the column too, or they hit the same limit. For new tables, use bigint (or bigserial, GENERATED … AS IDENTITY on a bigint) for ids.

When a sequence is the limit

A serial column made on PostgreSQL 10 or later has an integer sequence, which stops first:

ERROR:  nextval: reached maximum value of sequence "tickets_id_seq" (2147483647)

That’s code 2200H, not this one. The fix is the same pair of statements above. To see how close your sequences are to their limit:

SELECT schemaname, sequencename, data_type, last_value, max_value,
       round(100.0 * last_value / max_value, 1) AS pct_used
FROM pg_sequences
WHERE last_value IS NOT NULL
ORDER BY pct_used DESC;

Store big identifiers as text or bigint

Phone numbers, card numbers and external ids aren’t quantities: store them as text. Byte counts and millisecond timestamps belong in bigint.

Reproduce it

On PostgreSQL 18.6:

CREATE TABLE seo_err_pg.prices (id integer PRIMARY KEY, qty integer, small smallint);
INSERT INTO seo_err_pg.prices (id, qty) VALUES (7, 3000000000);
INSERT INTO seo_err_pg.prices (id, qty) VALUES (8, '3000000000');
SELECT 2147483647 + 1;
SELECT 50000 * 50000;
INSERT INTO seo_err_pg.prices (id, small) VALUES (9, 40000);
SELECT 9223372036854775807 + 1;

ERROR:  integer out of range
ERROR:  value "3000000000" is out of range for type integer
LINE 1: ...NSERT INTO seo_err_pg.prices (id, qty) VALUES (8, '300000000...
                                                             ^
ERROR:  integer out of range
ERROR:  integer out of range
ERROR:  smallint out of range
ERROR:  bigint out of range

SELECT 50000::bigint * 50000 returned 2500000000, and sum() over 2147483647 and 1 returned 2147483648 (a bigint). An integer column with a default of nextval() from a separately created sequence (a bigint one), after setval(…, 2147483647), failed on the next insert with integer out of range. A serial column at the same point failed with nextval: reached maximum value of sequence "tickets_id_seq" (2147483647); after the two ALTER statements above, the next insert got id 2147483648, and the sequence’s maximum had moved to the bigint limit by itself.

With \set VERBOSITY verbose, psql shows the code: ERROR: 22003: integer out of range. PostgreSQL 14.24 gives the same messages.

In Inlet

The structure editor changes a column’s type, shows the DDL, and warns when the change rewrites the table. Inlet lists each database’s sequences. When a statement fails, Inlet shows the server’s error; with your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix it, sending the schema, the SQL and the error, never rows.