loading...

Reset Postgres ID sequence in Rails

drbragg profile image Drew Bragg ・1 min read

Today I had to restore backup data from one of our SQL dumps to my local development database. For one reason or another the table id sequences didn't reset after the restore. So I was getting errors in my terminal like this:

PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "track_events_pkey"
DETAIL:  Key (id)=(3673) already exists.
: INSERT INTO "track_events" ("track_session_id", "action", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id" excluded from capture: DSN not set

Manually resetting it is actually quite easy.

Hop into your rails console and run:

ActiveRecord::Base.connection.tables.each do |t|
  ActiveRecord::Base.connection.reset_pk_sequence!(t)
end

I hope this helps someone else who runs into a similar issue!

Discussion

pic
Editor guide