postgresql - integer out of range

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.

Answer 1

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...