PostgreSQL-整数超出范围 [英] postgresql - integer out of range

问题描述

丝毫不知道为什么会这样。

Not the slightest idea why the hell this is happening..

我已经相应地建立了一个表格：

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'
);

错误为：错误：整数超出范围

我的意思是comon ...
甚至不确定从哪里开始调试。.我并没有磁盘空间和错误本身

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

推荐答案

SERIAL 列存储为 INTEGER s，使其最大值为2 ³¹ -1。因此，在插入大约20亿个字符之后，新的 id 值将不再适合。

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.

如果您期望有这么多插入在表的整个生命期内，使用 BIGSERIAL （内部为 BIGINT 创建），最多2个 ⁶³ -1）。

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

如果您稍后发现 SERIAL 并不大足够，您可以使用以下方法增加现有字段的大小：

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;

请注意，此处是 BIGINT BIGSERIAL （如序列不是实型）。并要记住，如果您的表中实际上有20亿条记录，这可能需要一些时间...

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

这篇关于PostgreSQL-整数超出范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！