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.

Solution

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:

DATABASE=> SELECT MAX(id) FROM "TableName";
 max 
-----
 607
(1 row)

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

DATABASE=> SELECT nextval('"TableName_id_seq"');
 nextval 
---------
       8
(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";
 setval 
--------
    607
(1 row)

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

DATABASE=> SELECT nextval('"TableName_id_seq"');
 nextval 
---------
     608
(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:

DATABASE=> \d
                          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)

Finally…

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

Resources

A few spots helped me find this solution:

Concise answer to a similar question on StackOverflow
List of Heroku CLI commands
Heroku PG Plugin docs
Someone illuminated the double quotes requirement