ERROR: integer out of range on Postgresql

Question

I've set up a table accordingly:

CREATE TABLE raw (
    id          SERIAL,
    regtime     float NOT NULL,
    time        float NOT NULL,
    source      varchar(15),
    sourceport  INTEGER,
    destination varchar(15),
    destport    INTEGER,
    blocked     boolean
); ... + index and grants

I've successfully used this table for a while now, and all of a sudden the following insert doesn't work any longer..

INSERT INTO raw(
    time, regtime, blocked, destport, sourceport, source, destination
) VALUES (
    1403184512.2283964, 1403184662.118, False, 2, 3, '192.168.0.1', '192.168.0.2'
);

The error is: ERROR: integer out of range

Not even sure where to begin debugging this.. I'm not out of disk-space and the error itself is kinda discreet.

@ClodoaldoNeto that is it.. copy and pasted.. Unix timestamp is 1403184512.2283964 and 1403184662.118 respectively, both are fine and does not affect the result in any way what so ever. Also they are placed at the beginning of both the insert clumn definitions and the value definitions. So the position is not the issue here. — Torxed
– Torxed, Commented Jun 19, 2014 at 14:32
Try select max(id) from raw. You also might try changing the type of ID from SERIAL (4 byte signed integer) to BIGSERIAL (8 byte signed integer). Share and enjoy. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jun 19, 2014 at 14:52
Cannot reproduce on PostgreSQL 9.3. The sequence underlying the "id" column is the most likely problem. What does select currval('raw_id_seq') return? (The name of your sequence might be different; mine is PostgreSQL's default.) — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Jun 19, 2014 at 14:54

Nick Barnes · Accepted Answer · 2018-05-25 14:54:44Z

102

SERIAL columns are stored as INTEGERs, giving them a maximum value of 2³¹-1. So after ~2 billion inserts, your new id values will no longer fit.

If you expect this many inserts over the life of your table, create it with a BIGSERIAL (internally a BIGINT, with a maximum of 2⁶³-1).

If you discover later on that a SERIAL isn't big enough, you can increase the size of an existing field with:

ALTER TABLE raw ALTER COLUMN id TYPE BIGINT;

Note that it's BIGINT here, rather than BIGSERIAL (as serials aren't real types). And keep in mind that, if you actually have 2 billion records in your table, this might take a little while...

edited May 25, 2018 at 14:54

answered Jun 23, 2014 at 12:59

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Torxed Over a year ago

The syntax i used was ALTER TABLE raw ALTER COLUMN id TYPE BIGINT but i guess it's all the same :)

Nick Barnes Over a year ago

@Torxed Yeah, the COLUMN keyword is optional, but it does make things a little clearer. Updated ;)

Gaetano Mendola Over a year ago

The alter table is not practically doable on a live database indeed it rebuilds the table locking it for the full duration of it.

Nick Barnes Over a year ago

@Gaetano: True, if you caught this early then you might want something more elaborate to avoid the downtime (e.g. add a trigger-maintained duplicate of id and rename it once it's populated). But if every insert is already failing with an integer out of range error, then your system is not exactly "live", and I think you'll just need to wait it out...

Ciro Santilli OurBigBook.com · Accepted Answer · 2025-09-03 08:46:53Z

You may also need to do some typecasts to get rid of the error due to operation overflows

I just wanted to illustrate that in some cases, the issue is not just that you need a BIGINT column, but that some intermediate operation is overflowing.

For example, the following gives ERROR: integer out of range, even though we made the column BIGINT:

CREATE TABLE tmp(i BIGINT);
INSERT INTO tmp SELECT 2147483647 + 1;

The problem is that 2147483647 = 2**31 - 1, the maximum integer that fits into INTEGER, so when we add 1 it overflows and we get the error.

The same issue happens if we just SELECT without any tables involved:

SELECT 2147483647 + 1;

To solve the issue, we could typecast either as:

SELECT 2147483647::BIGINT + 1;

or as:

SELECT 2147483647 + 1::BIGINT;

so we understand that so long one of the operators is BIGINT, the result gets implicitly typecast without error.

It is also worth noting that

SELECT 2147483648 + 1;

does not give any error because when we use a 2147483648 literal, that doesn't fit into INTEGER, so PostgreSQL assumes it is BIGINT by default.

generate_series typecasting

Another case where the issue might come up is when using generate_series to generate some large test data, this is what brought me here in the first place, e.g.:

SELECT i + 1 FROM generate_series(2147483647, 2147483647) AS s(i);

gives the error for similar reasons as above, because if the arguments of generate_series are INTEGER, then so are the returned values. One good clean solution in this case is to typecast the arguments of generate_series to BIGINT as in:

SELECT i + 1 FROM generate_series(2147483647::BIGINT, 2147483647::BIGINT) AS s(i);

Tested on PostgreSQL 16.6, Ubuntu 24.04.1.

Collectives™ on Stack Overflow

ERROR: integer out of range on Postgresql

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related