U130: Integer out of range
SQLSTATE: 22003 (Class 22 — Data Exception: numeric_value_out_of_range)
Example Postgres Log Output:
ERROR: integer out of range STATEMENT: INSERT INTO x(y) VALUES (10000000000000)
You've tried to INSERT an integer value into a table that exceeds the range of the underlying integer data type in the specified column.
The easiest example of this is when you literally insert a too large value into the database. In such cases it may be either a bug in your ingestion flow, or the wrong data type in the database.
However, the most common case where this occurs is when using sequences
SERIAL data type) for your primary key column, and inserting so many
records that the limits of the underlying data type are reached, usually
2147483647 records with the
SERIAL data type.
You may also see this problem on any associated tables that reference the
column using an integer sequence for its primary key, where you also need to
watch for the column value not exceeding
2147483647 for an
integer data type.
Unfortunately many web development frameworks and ORMs still default to using
integer columns for their primary keys that use sequences, which means you
will run into this problem at some point in the future.
Monitoring sequence values is highly recommended for this purpose, e.g. by doing the following:
SELECT last_value FROM my_table_id_seq;
You may also choose to simply always use
bigint for all sequence-related columns,
to avoid this problem from occurring.
Note that ignoring this error will likely result in data loss, so unless you can recover from insert failures, this error needs to be addressed immediately.
Increase the size of the column by doing the following:
ALTER TABLE x ALTER COLUMN y TYPE bigint;
Note that this will take an exclusive lock as it rewrites the table, so you may need to add a new column first and backfill it, like this:
--- Deploy this schema change and have your application always write to y_new as well: ALTER TABLE x ADD COLUMN y_new TYPE bigint; --- Backfill all existing columns: UPDATE x SET y_new = y WHERE y_new IS NULL; --- Drop old column and rename the new column to take its place ALTER TABLE x DROP COLUMN y; ALTER TABLE x ALTER COLUMN y_new RENAME TO y;
When taking the second approach be careful to also specify any additional parameters (e.g. default value for sequence), as well as setting any NOT NULL constraints at the end.
- Jake Swanson: The Night the PostgreSQL IDs Ran Out (contains a creative approach using negative IDs to temporarily workaround this issue - worth a read!)
- Postgres Documentation: Data Types - Numeric Types
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →