I've an existing rails application and one of column's has existing semi-colon separated string. I want to convert the same into an array with an empty array as default.
Also, for rows which already have data in the semi-colon separated string format we need it to get converted into a proper array.
psql or PostgreSQLRails 4.2I solved it like this:
class ChangeQualificationToArray < ActiveRecord::Migration
def change
change_column :coaches, :qualifications, "varchar[] USING (string_to_array(qualifications, ';'))"
end
end
The best way to do it efficiently, is by using PSQL query directly as follows:
class ChangeQualificationToArray < ActiveRecord::Migration
def change
execute <<-SQL
ALTER TABLE "coaches" ALTER COLUMN "qualifications" TYPE text[] USING string_to_array("coaches"."qualifications", ',')::text[];
SQL
end
end
The above is a pretty common scenario in development of any application and a lot of people face problems while doing the same in Rails.
In my opinion the easiest way to get all this done is:
rails g migration changeSomethingToArrayIn the new migration file, add the following code.
class ChangeQualificationToArray < ActiveRecord::Migration
def change
rename_column :coaches, :qualifications, :qualifications_text
add_column :coaches, :qualifications, :text, default: []
coaches = Coach.all
coaches.each do |c|
c.qualifications = convert_to_array(c.qualifications_text)
c.save
end
remove_column :coaches, :qualifications_text
end
private
def convert_to_array(string)
string.split(';')
# This can be changed to `,` or whatever you're using to split your string.
end
end