Postgresql error: insert operation returns id exists in Python

Question

I tried to insert name into database.

name = 'test'
cur.execute("INSERT INTO scholars(name) VALUES('{}') returning id".format(name))
id = cur.fetchone()
print(id)

error message:

psycopg2.IntegrityError: duplicate key value violates unique constraint "idx_16514_primary"
DETAIL:  Key (id)=(2321) already exists.

When id=2301, it succeed,and return ID. After that,id += 1and returns the error below.

Did you insert values manually without updating the sequence properly? — Sami Kuhmonen, Oct 12 '16 at 12:10
There might be something wrong with your id sequence, run `SELECT currval(pg_get_serial_sequence('scholars', 'id'));` to show the most recent value created by id sequence. — Kane Blueriver, Oct 12 '16 at 12:18

score 1 · Answer 1 · answered Oct 12 '16 at 12:09

1

Seems to me that You already have record with ID 2321 in your DB. Can You check with SELECT id FROM scholars?

answered Oct 12 '16 at 12:09

Fejs

But it should be manually add id when i insert a new data. – M1nt_zwy Oct 12 '16 at 13:18
My guess: You have deleted some records, or You have manually created some IDs, so automatic generator got into problems. – Fejs Oct 12 '16 at 13:44

score 0 · Answer 2 · edited May 23 '17 at 12:01

Thanks @e4c5, @kxxoling

Something was wrong with my id sequence.

I fixed it by doing this.

SELECT MAX(id) FROM scholars;

=> 11518

SELECT nextval('scholars_id_seq');

=> 2324 # here it is lower than max(id)

SELECT setval('scholars_id_seq', (SELECT MAX(id) FROM scholars));

=> 11518 # fix it

SELECT nextval('scholars_id_seq');

=> 11519 # normal

2 Answers2