# ERROR 22003: Numeric Value Out of Range in Postgres Source: https://www.bytebase.com/reference/postgres/error/22003-numeric-value-out-of-range/ --- ## Error Message ```sql ERROR: integer out of range SQLSTATE: 22003 ``` The wording shifts with the type and the operation, but the SQLSTATE stays `22003`: ```sql ERROR: smallint out of range ERROR: bigint out of range ERROR: value "9999999999" is out of range for type integer ERROR: numeric field overflow DETAIL: A field with precision 5, scale 2 must round to an absolute value less than 10^3. ``` PostgreSQL never clamps. Where MySQL in non-strict mode would quietly trim the value and move on, Postgres rejects the write. That is the safer behavior, and it means a 22003 is always telling you something real. ## What Triggers This Error A number does not fit the target type. Six paths lead here, and each one wants a different fix: - **Inserted value exceeds the integer type:** a literal or parameter is larger than `smallint`, `integer`, or `bigint` allows. - **Arithmetic overflow:** a multiplication, a `SUM()`, or a running total passed the result type. - **`NUMERIC(precision, scale)` overflow:** the integer part has more digits than `precision − scale` permits. - **Cast of an out-of-range value:** `'9999999999'::int` or `1e20::bigint`. - **`SERIAL` or identity sequence hit the ceiling:** an `integer` key ran out at about 2.1 billion. - **`COPY` or bulk load carries a bad value:** one row aborts the batch. ## Fix by Scenario ### Inserted value exceeds the integer type PostgreSQL's integer types are bounded: `smallint` to 32,767, `integer` to 2,147,483,647, `bigint` to roughly 9.2 quintillion. ```sql INSERT INTO sensors (reading) VALUES (40000); -- column is smallint -- ERROR: smallint out of range ``` Check the type, then widen it if the value is legitimate: ```sql SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'sensors' AND column_name = 'reading'; ALTER TABLE sensors ALTER COLUMN reading TYPE integer; ``` If the number is wrong (a unit error, a bad parse), fix the source. Widening the column to absorb bad data just moves the problem downstream. ### Arithmetic overflow The stored columns fit, but an expression over them does not: ```sql SELECT SUM(bytes_transferred) FROM traffic; -- integer SUM overflows UPDATE counters SET n = n * 1000000; -- the product exceeds integer ``` Cast to a wider type before the operation. One thing that surprises people: `SUM` over `integer` returns `bigint`, but `SUM` over `bigint` can still overflow. When a total has no natural ceiling, go to `numeric`. ```sql SELECT SUM(bytes_transferred::numeric) FROM traffic; ALTER TABLE counters ALTER COLUMN n TYPE bigint; ``` ### NUMERIC(precision, scale) overflow `NUMERIC(5,2)` allows three digits before the decimal point, because `5 − 2 = 3`. So `1000.00` overflows, small as it looks. ```sql INSERT INTO invoices (total) VALUES (1000.00); -- column is NUMERIC(5,2) -- ERROR: numeric field overflow -- DETAIL: A field with precision 5, scale 2 must round to an absolute value less than 10^3. ``` Read the `DETAIL` line. It hands you the exact bound the value broke, which makes this the easiest 22003 to diagnose. Then raise the precision to cover real amounts: ```sql ALTER TABLE invoices ALTER COLUMN total TYPE NUMERIC(12,2); ``` ### Cast of an out-of-range value Explicit casts overflow when the source is too big for the target: ```sql SELECT '9999999999'::integer; -- ERROR: value "9999999999" is out of range for type integer SELECT (1e20)::bigint; -- ERROR: bigint out of range ``` Cast to a type that holds the value, or validate the input and reject it before the cast: ```sql SELECT '9999999999'::bigint; -- fits ``` ### SERIAL or identity sequence hit the ceiling The 2.1-billion wall. An `integer` or `serial` primary key exhausts its range, and since sequences never reuse the gaps from deleted rows, a high-churn table can get there with far fewer than 2.1 billion rows actually present. ```sql SELECT last_value FROM my_table_id_seq; ``` Migrate the column to `bigint`. On a large table this rewrites every row, so schedule a window or use a low-lock migration tool rather than running a plain `ALTER` against live traffic. ```sql ALTER TABLE events ALTER COLUMN id TYPE bigint; ALTER SEQUENCE events_id_seq AS bigint; ``` ### COPY or bulk load carries a bad value A single oversized value fails the entire `COPY`: ```sql COPY metrics FROM '/tmp/metrics.csv' CSV HEADER; -- ERROR: value "5000000000" is out of range for type integer -- CONTEXT: COPY metrics, line 8120, column count ``` Postgres gives you the line number. Look at it directly, then fix the data or widen the column: ```bash sed -n '8120p' /tmp/metrics.csv ``` For loads that run regularly, stage into a `numeric` or `bigint` table, validate, then insert into production. One bad row should not be able to sink the whole batch. ## Prevention - Pick integer types from the real domain. As of PostgreSQL 17, reach for `bigint` on any identity key that could pass a few hundred million rows over its life. `GENERATED ALWAYS AS IDENTITY` over `bigint` is the safe default for anything high-churn. - Size `NUMERIC(p,s)` from the largest legal amount, not the common one. Money and metric columns overflow when `p` is set too low, and the `DETAIL` line will tell you by how much. - Promote unbounded aggregates to `numeric`. A `SUM` over `bigint` can still overflow. - Validate numeric ranges in the application and in schema review, before the write reaches PostgreSQL. - For bulk loads, stage into a wider table, validate, then insert. Do not bet a whole `COPY` on the cleanest row in the file. > **Note:** [Bytebase's SQL Review](https://docs.bytebase.com/sql-review/review-rules/) can flag risky integer choices, such as an `integer` identity primary key, during change review, catching the most common 22003 sources before they ship. See also [ERROR 22001: String Data Right Truncation](/reference/postgres/error/22001-string-data-right-truncation) for the string-length version of the same problem.