psycopg2.errors.NumericValueOutOfRange: integer out of range

Question

I am trying to insert data into users table:

cursor.execute("""CREATE TABLE IF NOT EXISTS users (
        name TEXT NOT NULL, 
        id BIGINT NOT NULL, 
        xp INT NOT NULL
    )""")
    for guild in bot.guilds:
        for member in guild.members:
            if cursor.execute(f"SELECT id FROM users WHERE id = {member.id}") is None:
                if member.id not in bots:
                    cursor.execute("INSERT INTO users (name, id, xp) VALUES ('{}', {}, {});".format(member, member.id, 0))
    connect.commit()
    connect.close()

But I keep getting the error:

File "C:\Users\belog\hat_dispenser\main.py", line 58, in on_ready
    cursor.execute("INSERT INTO users (name, id, xp) VALUES ('{}', {}, {})".format(member, member.id, 0))
psycopg2.errors.NumericValueOutOfRange: integer out of range

I tried to give the column "id" data types such as BIGINT, INT, BIGSERIAL, SERIAL. But I still get an "integer out of range" error. How to fix it?

Gamopetalous

maybe you get the error because of the value of the field xp – Copenhagen

You are going to need to print the value for the variable member.id or look in the Postgres log to see what is being passed in. Best bet it is something that is larger then 9223372036854775807. – Median

I checked and the maximum id was 910096720388583444. But even when I use the INT and SERIAL datatypes, I still get the "psycopg2.errors.NumericValueOutOfRange: integer out of range" error. – Gamopetalous

You would as 910096720388583444 exceeds the limit(2147483647) for int and serial. You would need bigint/bigserial. Start with the premise that the error is telling you the truth and work backwards from there. Me I start with the Postgres log as that shows you what is actually hitting the database and has the full error message. – Median

Answer 1

Do check whether this error is caused by the id column. Probably it's xp. My solution was to give the BigInteger type to an incorrect column.

Segno