ruby - Rails Migration changing column to use Postgres arrays

Question

I am trying to change a column in my database so that it can use the Postgres array data type. Currently the table column is of type string.

I am using the following migration to convert it:

def change
  change_column :table, :dummy_column, :text, array: true, default: []
end

But I get the following error:

bundle exec rake db:migrate
rake aborted!
An error has occurred, this and all later migrations canceled:

PG::Error: ERROR:  column "dummy_column" cannot be cast automatically to type     character varying[]
HINT:  Specify a USING expression to perform the conversion.
: ALTER TABLE "table" ALTER COLUMN "dummy_column" TYPE character varying(255) 
Tasks: TOP => db:migrate

Answer 1

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).