Rails change existing text column to array

Question

Using the Postgres database, added a text field staff_ids to branches table:

add_column :branches, :staff_ids, :text

In controller added this field to branch_params list:

:staff_ids => []

Data has been saved in this column like ["","32","52"]. When querying this field I got an error saying staff_ids should be an array

Branch.where("? = ANY(staff_ids)", '20')

ActiveRecord::StatementInvalid: PG::WrongObjectType: ERROR:  op ANY/ALL (array) requires array on right side

Actually, I forgot to add array: true option in the migration when adding staff_ids field. Now added another migration to change this column and tried to add array: true option:

change_column :branches, :staff_ids, :text, array: true

But the migration failed with an error:

PG::DatatypeMismatch: ERROR:  column "staff_ids" cannot be cast automatically to type text[]

Now either I want to update the where clause so that it filters the branches based on staff_ids without adding the array: true or fix the migration mentioned above.

Any suggestion / idea ?

you can try fixing it with SQL on db level, but you probably want to fix a migration?.. — Vao Tsun, May 03 '17 at 07:36
Want to fix it in a migration in order to execute it on staging and prod environments — Arif, May 03 '17 at 07:42
Possible duplicate of [Changing a column from string to string array in postgresql](http://stackoverflow.com/questions/14782148/changing-a-column-from-string-to-string-array-in-postgresql) — Eyeslandic, May 03 '17 at 07:44

Shabini Rajadas · Answer 1 · 2017-05-03T09:08:03.647

You can add the following in your migration,

def up
    change_column :branches, :staff_ids, :text, array: true, default: [], using: "(string_to_array(staff_ids, ','))"
end

def down
    change_column :branches, :staff_ids, :text, array: false, default: nil, using: "(array_to_string(staff_ids, ','))"
end

Defining up and down methods will help to reverse your migration at any time, if the change to an array was not required.

Rails change existing text column to array

1 Answers1