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
– Vao Tsun, Commented May 3, 2017 at 7:36
Want to fix it in a migration in order to execute it on staging and prod environments — Arif
– Arif, Commented May 3, 2017 at 7:42
Possible duplicate of Changing a column from string to string array in postgresql — Eyeslandic
– Eyeslandic, Commented May 3, 2017 at 7:44

Shabini Rajadas · Accepted Answer · 2017-05-03 09:08:03Z

0

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.

edited May 3, 2017 at 9:08

answered May 3, 2017 at 8:58

Shabini Rajadas

7718 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Rails change existing text column to array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related