Postgres integer out of range

Question

I am getting a value "1574799549227" is out of range for type integer error when trying to insert into a brand new table on a brand new database. I am using node with the pg module.

db
.query(`INSERT INTO users (uuid, email, password, created_at, updated_at) VALUES ($1, $2, $3, to_timestamp($4 / 1000), to_timestamp($4 / 1000))`,
            [uuid, email, password, Date.now()])
.catch((err) => {
    console.log(err);
});

This is the table

CREATE TABLE public.users
(
    id serial,
    uuid uuid,
    email character varying,
    password character varying,
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    CONSTRAINT user_id_pk PRIMARY KEY (id)
)

I have also tried reseting the sequence number for the id field

@nacho I have switched it to bigserial, it doesn't fix the issue. It also shouldn't be an issue as the database and table are brand new.. — Cameron Hanton
– Cameron Hanton, Commented Nov 26, 2019 at 20:44

Cameron Hanton · Accepted Answer · 2019-11-26 21:12:08Z

2

I ended up figuring it out. Turns out that it was the timestamps I had. I was using to_timestamp($4 / 1000) when I should of been using to_timestamp($4 / 1000.0)

answered Nov 26, 2019 at 21:12

Cameron Hanton

1221 silver badge7 bronze badges

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

Comments

Rishabh Sagar · Accepted Answer · 2019-11-26 20:51:18Z

1

Please see the official documentation for Numeric types. Serial datatype has a range of 1 to 2147483647.

SERIAL columns are stored as INTEGERs, giving them a maximum value of 231-1. So after ~2 billion inserts, your new id values will no longer fit therefore please ensure that you are inserting that many rows during the lifetime of the table.

If you are altering table, please remember to use BIGINT rather than BIGSERIAL.

ALTER TABLE users ALTER COLUMN id TYPE BIGINT;

Most likely cause is that the value in id column is out of this range.

edited Nov 26, 2019 at 20:51

answered Nov 26, 2019 at 20:46

Rishabh Sagar

1,8242 gold badges18 silver badges27 bronze badges

3 Comments

Cameron Hanton Over a year ago

Maybe I didnt express it clearly. The table is new, there is no data in it already. Id is serial so it auto increments so theoretically it should be at 1

Rishabh Sagar Over a year ago

Have you tried recreating the table with col id as BIGSERIAL or altering the table to change the id col to BIGINT?

Cameron Hanton Over a year ago

Yes, as I explained to nacho above. I have tried switching them

Collectives™ on Stack Overflow

Postgres integer out of range

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related