U130: Integer out of range

Example Postgres Log Output:

ERROR: integer out of range
STATEMENT: INSERT INTO x(y) VALUES (10000000000000)

Explanation:

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 (i.e. SERIAL data type) for your primary key column, and inserting so many records that the limits of the underlying data type are reached, usually that means 2147483647 records with the integer or 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.

Recommended Action:

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.

Learn More:

• 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