How to define the sequence to use when creating a table in ActiveRecord migration in Ruby on Rails 5.2?

Question

I need to assign a specific Postgres sequence to the ID field of my table. In the model, I tried to define the following setup which has no effect on Posgres:

class MyObject < ActiveRecord::Base

self.sequence_name = "global_seq"

Usually, a table definition in ActiveRecord migrations start with

create_table "objects", id: :serial, force: :cascade do |t|

which generates a Postgres definition of column default value as

default nextval('objects_id_seq'::regclass)

How can I specify in the migration that the nextval() function should rely on another sequence ?

Answer 1

You can change the default in a migration:

change_column :my_objects, :id, :integer, default: -> { "nextval('global_seq')" }

You might want to use :bigint instead of :integer depending on how your sequence and tables are set up. You have to use a lambda for the :default option to get the raw nextval('global_seq') expression into the database.

You might want to drop the old sequence as well, AFAIK you'd have to use connection.execute('drop sequence ...') for that.

If you're skipping the default :id step in your create_table then you can do it all when you manually create the :id column:

create_table :my_objects, id: false do |t|
  t.bigint :id, null: false, default: -> { "nextval('global_seq')" }
  t.primary_key :id
  ...
end

Again, the choice between t.bigint and t.integer depends on how big you want your PK to be.

Answer 2

I'm afraid Rails' migrations have no built-in command to set sequence to a column explicitly. It's very database specific.

However it can be done using plain SQL:

class ChangeSequenceOfObjectId < ActiveRecord::Migration[5.0]
  def self.up
    execute <<-SQL
      CREATE SEQUENCE global_seq;
      ALTER TABLE objects ALTER COLUMN id SET DEFAULT nextval('global_seq');
      ALTER SEQUENCE objects_id_seq OWNED BY NONE;
      ALTER SEQUENCE global_seq OWNED BY objects.id;
    SQL
  end

  def self.down
    execute <<-SQL
      ALTER TABLE objects ALTER COLUMN id SET DEFAULT nextval('objects_id_seq');
      ALTER SEQUENCE objects_id_seq OWNED BY objects.id;
      ALTER SEQUENCE global_seq OWNED BY NONE;
      DROP SEQUENCE global_seq;
    SQL
  end

If global_seq sequence is created in other migration then just remove corresponding lines regarding its creating / deleting.

Also ALTER SEQUENCE global_seq OWNED BY commands can be removed if you want to leave global_sql sequence as 'free-standing' (staying in the db even if objects table is dropped).