python - postgresql 插入时报错ID已存在

问题描述

问 题

作插入操作：

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

报错如下：

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

id=2301时插入成功，并且成功返回ID。
之后插入一次，id+1，报错ID已存在。

数据库插入操作，ID不是自动寻找最大值，然后自增么？

解决方案

Here i got the answer How to reset postgres' primary key sequence when it falls out of sync? to this question.

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 # done!

这篇关于python - postgresql 插入时报错ID已存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！