Rails migration array without default

Question

Rails 5, Postgres 9

I have >10 millions of records for model. I need to add new field with array type. From docs I can add new migration as

add_column :model, :new_field, :string, array: true, default: []

But after it Database blocked because of many Alter operations with setting default value.

Is it real to create new array field without (default: []) value?

Isn't the default in postgresql an empty array anyway, so why explicitly specify that? — nathanvda
– nathanvda, Commented Nov 23, 2019 at 21:41

PGill · Accepted Answer · 2019-11-24 04:52:10Z

Add a column the table with default value of null
Update the rows in batches(default 1000) and set [] as the value
Set the NOT NULL on the column

class AddCategoriesToProducts < ActiveRecord::Migration[6.0]
  disable_ddl_transaction!

  def up
    ActiveRecord::Base.transaction do
      add_column :products, :categories, :string, array: true

      execute <<~SQL
        ALTER TABLE products ALTER COLUMN categories SET DEFAULT '{}';
      SQL
    end

    Product.find_in_batches(batch_size: 1000).with_index do |products, index|
      puts "Processing #{index + 1}"
      Product.where(id: products.map(&:id)).update_all(categories: [])
    end

    ActiveRecord::Base.transaction do
      execute <<~SQL
        ALTER TABLE products ALTER COLUMN categories SET NOT NULL;
      SQL
    end
  end

  def down
    ActiveRecord::Base.transaction do
      remove_column :products, :categories
    end
  end
end

This is a slow migration and you have to wait for it to finish but it’ll prevent locking of the table.

Collectives™ on Stack Overflow

Rails migration array without default

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related