Simon Willison’s Weblog

Fixing sequence problems in PostgreSQL

21st April 2004

This one’s mainly for my own future reference. The following error message in PostgreSQL:

ERROR:  Cannot insert a duplicate key into unique index auth_permissions_pkey

Is caused by a table with an automatically incremented primary key (set up using the SERIAL keywords in the table creation statement) getting out of sync somehow with the sequence that tracks the next available primary key. You can re-sync the sequence with the following query:

SELECT setval('auth_permissions_id_seq', 
  (SELECT max(id) FROM auth_permissions));