Rails Migration Changing Column to Use Postgres Arrays

Rails Migration changing column to use Postgres arrays

PostgreSQL doesn't know how to automatically convert a column of varchar into an array of varchar. It doesn't know what you might intend, because it has no way to know what format you think the current values are in.

So you need to tell it; that's what the USING clause is for.

ActiveRecord doesn't seem to explicitly support the USING clause (not surprising, as it barely supports even the most basic database features). You can specify your own SQL text for the migration, though.

Assuming your strings are comma separated and may not themselves contain commas, for example:

def change
  change_column :table, :dummy_column, "varchar[] USING (string_to_array(dummy_column, ','))"
end

(I don't use Rails myself and haven't tested this, but it's consistent with the syntax used in examples elsewhere).

Rails Migration - Change Integer Column to Array Integer - Postgres

You need to specify integer array with column name in using keyword.

change_column :providers, :benefit_type, :integer, array: true, default: [], using: 'ARRAY[benefit_type]::INTEGER[]'

Change column type from text to array of string (PG::DatatypeMismatch: ERROR: column)

According to this link the following code works for me

def up
  change_table :hotels do |t|
    t.change :emails, :string, array: true, default: [], using: "(string_to_array(emails, ','))"
    t.change :phones, :string, array: true, default: [], using: "(string_to_array(phones, ','))"
  end
end

Rails: Adding migration to add an array (default empty)

Rails 4 the PostgreSQL Array data type

In terminal

$ rails generate migration AddTagsToProduct tags:string

Migration file:

class AddTagsToProduct < ActiveRecord::Migration
  def change
    add_column :products, :tags, :string, array: true, default: []
  end
end

https://coderwall.com/p/sud9ja/rails-4-the-postgresql-array-data-type

Rails: Change existing integer column to array data type

After digging around for postgres casting functions & trying several things that didn't work, I found the answer.

using: 'ARRAY[value]::INTEGER[]'

The hint was close, but off by just enough to be confusing.

How to migrate Postgres array data in Rails

Running:

Template.reset_column_information

After the column is created and before the data migration should refresh the cache and discover the new column.
This is the same for any single migration file which does both column manipulation and data migrations.

The alternative is to split this over 2-3 migrations. Like so:

1. Create column
2. Data migration
3. Column remove & rename

This second approach has the added benefit that if your data migration fails for any reason you’re not left in a pseudo-state where the first column has been created already. This state means that trying to rerun the migration will fail as the column already exists.

Rails migration array without default

1. Add a column the table with default value of null
2. Update the rows in batches(default 1000) and set [] as the value
3. 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.

---

Related Topics