Jon Cairns

I've been involved in writing a rake task to migrate an old database schema to a similar but new schema in a Rails project. The new schema was similar enough that we could carry across all the primary keys from the old tables, and it saved a lot of time to re-use them. However, the problem with doing this is that the auto increment sequence does not get updated when you add a value - you have to manually reset it.

This can be done in Rails, but only by using raw SQL. The code to execute is:

table = 'my_table'
auto_inc_val = 10    # New auto increment start point
ActiveRecord::Base.connection.execute(
  "ALTER SEQUENCE #{table}_id_seq RESTART WITH #{auto_inc_val}"
)

If you want to run this for all tables (as we did after our migration script completed), you can loop through all the available tables and get the most recent ID:

ActiveRecord::Base.connection.tables.each do |table|
  result = ActiveRecord::Base.connection.execute("SELECT id FROM #{table} ORDER BY id DESC LIMIT 1")
  if result.any?
    ai_val = result.first['id'].to_i + 1
    puts "Resetting auto increment ID for #{table} to #{ai_val}"
    ActiveRecord::Base.connection.execute("ALTER SEQUENCE #{table}_id_seq RESTART WITH #{ai_val}")
  end
end