Resetting Primary Key Sequence In Heroku Postgres Tables After Migration


I recently deployed an application on Heroku and in doing so, migrated all of the app’s data from my local machine to the Heroku Postgres server. I did this by exporting/importing csv files, however I quickly noticed that this method doesn’t update the sequence for any serially incremented primary keys. As a result, whenever I tried to insert new rows into a table with an auto-incrementing primary key, I would get an error: duplicate key value violates unique constraint. This was because Postgres was generating a primary key value that already existed.


This can be fixed from the Postgres command line. For a Heroku app, this needs to be installed first:

  • Open Heroku command line: heroku login …then log in
  • Install postgres plugin: heroku plugins:install heroku-pg-extras
  • Connect to Heroku Postgres server for app-name: heroku pg:psql --app app-name

To see if sequencing is actually the problem, you can check the current maximum id:

(1 row)

…and compare this to the next value in the sequence:

DATABASE=> SELECT nextval('"TableName_id_seq"');
(1 row)

In this case, the next value should be 608!

Take note of the single and double quotes around the table name. The table name always needs to have double quotes around it. It’s then surrounded by single quotes for the nextval() query.

Reset the value:

DATABASE=> SELECT setval(pg_get_serial_sequence('"TableName"', 'id'), max(id)) FROM "TableName";
(1 row)

Re-check the sequence next value to confirm it worked:

DATABASE=> SELECT nextval('"TableName_id_seq"');
(1 row)

Check All Sequences

You may have more than one table that uses an auto-incremented id, and will need to do the above more than once. You can use the \d command to see a list of all of your tables and sequences to make sure you don’t miss any:

List of relations
Schema | Name | Type | Owner
public | TableName | table | herokupgdbowner
public | TableName_id_seq | sequence | herokupgdbowner
public | TableNameTwo | table | herokupgdbowner
public | TableNameTwo_id_seq | sequence | herokupgdbowner
public | TableNameThree | table | herokupgdbowner
public | TableNameThree_id_seq | sequence | herokupgdbowner
public | SequelizeMeta | table | herokupgdbowner
public | Sessions | table | herokupgdbowner
(8 rows)


Try inserting data again to make sure everything’s working properly.


A few spots helped me find this solution: