Bobby Meyer
developer/designer

postgresql | rails | ruby

Resetting PostgreSQL pk_sequences in Rails

March 6, 2021

I have recently been working on a project that required transferring a large amount of data to a new PostgreSQL database with a dissimilar schema. I needed to import these records with their primary keys intact. Because of this, the primary key sequence was no longer in sync with the values in the database, and attempting to create new records can lead to this error:

ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint

To solve this, you need to reset the pk_sequence to the max value in the existing data. You can do this for a single table in the console by running:

ActiveRecord::Base.connection.reset_pk_sequence!('table_name')

More efficiently, you can do this for all tables with the following command.

ActiveRecord::Base.connection.tables.each do |t|
  ActiveRecord::Base.connection.reset_pk_sequence!(t)
end
back to posts
bobbymeyer.com © 2023 Bobby Meyer