středa 12. září 2012

How restart sequence in PostgreSQL?

After importing some data to PostgreSQL database my Django application started throwing exception while I tried insert new row to table:
IntegrityError at /url/
duplicate key value violates unique constraint "table_name_pkey"
Problem was in sequence, which returned already taken ids.

Diagnosis:

SELECT MAX(id) FROM table_name;
SELECT nextval('table_name_id_seq');
/* Max(id) is greater than nextval. */

Fix:

SELECT setval('table_name_id_seq', (SELECT max(id) FROM table_name)+1);
/* or set next id exacly */
SELECT setval('table_name_id_seq', 123456);
/* or */
ALTER SEQUENCE table_name_id_seq RESTART WITH 123456; 

Žádné komentáře:

Okomentovat