Change autoincrement id in Rails using Postgresql

The aim of this post is to remind future me how to modify the default auto-increment id rails creates behind scene when running migrations.

Here is a simple migration of table users and if you scour the internet you will find similar solutions but none worked for me using Rails 5.2

class CreateUsers < ActiveRecord::Migration[5.2]
  def up
    create_table :users do |t|
      t.string :name
    end
    execute "ALTER SEQUENCE users_id_seq MINVALUE 1000 OWNED BY users.id START WITH 1000 RESTART 1000;"
  end

  def down
    drop_table :users
  end

end

Rails by default creates a sequence in the form of pluraTable_columnID_seq. In my example the sequence is users_id_seq. The original sequence is created with starting from 1 as default so we need to alter the sequence in order to start from a different number e.g 1000.

Bear in mind this is a brand new table so the very 1st record inserted in table will have the id:1000. One neat trick is to add OWNED BY users.id which causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well (handy when doing rollbacks).

I initially added just START WITH 1000 but I was getting errors so had add RESTART 1000 and that fixed the migration.

Hope you find this tip useful.

Comments

comments powered by Disqus