Change autoincrement id in Rails using Postgresql

A nice trick to change starting number id

Alberto Negron true
11-10-2018

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.

Reuse

Text and figures are licensed under Creative Commons Attribution CC BY 4.0. The figures that have been reused from other sources don't fall under this license and can be recognized by a note in their caption: "Figure from ...".

Citation

For attribution, please cite this work as

Negron (2018, Nov. 10). Data Addict's Secret Diary: Change autoincrement id in Rails using Postgresql. Retrieved from http://www.dataaddict.me/posts/2018-11-10-change-autoincrement-id-in-rails-using-postgresql/

BibTeX citation

@misc{negron2018change,
  author = {Negron, Alberto},
  title = {Data Addict's Secret Diary: Change autoincrement id in Rails using Postgresql},
  url = {http://www.dataaddict.me/posts/2018-11-10-change-autoincrement-id-in-rails-using-postgresql/},
  year = {2018}
}