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

Answer 2

Using Rails 4.2 on postgresql 9.4 I was looking to do this and preserve my pre-existing string data as the first element in one element arrays.

It turns out that postgresql cannot coerce a string into a text array without a USING expression to tell it how.

After much fiddling with delicate postgres syntax, I found a good middle way with active record:

def change
  change_column :users, :event_location, :text, array: true, default: [], using: "(string_to_array(event_location, ','))"
end

The only direct postgresql there is the (string_to_array() ) function call. Here are the docs on that--note that you have to supply a delimiter.