Postgresql error: insert operation returns id exists in Python [duplicate]

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
– Sami Kuhmonen, Commented Oct 12, 2016 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
– Kane Blueriver, Commented Oct 12, 2016 at 12:18

Fejs · Accepted Answer · 2016-10-12 12:09:24Z

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, 2016 at 12:09

Fejs

2,8983 gold badges27 silver badges44 bronze badges

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

2 Comments

But it should be manually add id when i insert a new data.

My guess: You have deleted some records, or You have manually created some IDs, so automatic generator got into problems.

Community · Accepted Answer · 2017-05-23 12:01:34Z

0

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

CommunityBot

11 silver badge

answered Oct 12, 2016 at 13:25

M1nt_zwy

9373 gold badges10 silver badges19 bronze badges