If I've got a production database that has "types" stored as string, but I want to convert that column to an integer for enum.
I've googled/SO'd, and I see that I can CAST, but not sure what that does exactly.
If it's not hard, I'd love ot use rails enum, but otherwise, maybe I should stick with my string schema...
Please advise!
You can rename existing column, create a new one called "types" (integer), then write a script that stores appropriate integer value in the new column, and then drop the old column.
The migration will look like this:
class FixTypes < ActiveRecord::Migration
def change
rename_column :table_name, :types, :old_types
add_column :table_name, :types, :integer
end
end
Then write a script that sets the value of "types" based on "old_types":
Model.all.each do |entry|
entry.types = %w(status1 status2 status3 status4).index(entry.old_types)
entry.save!
end
And then drop the "old_types" column.
I had the same problem, and I figured you can edit the migration file to update the new column with the values you want to use as enums.
It should look something like this:
class UpdateColumnNameInTableName < ActiveRecord::Migration[6.1]
def up
add_column :table_name, :new_column_name, :integer
# Define the new enum values and their corresponding integers
enum_values = {
value1: 0,
value2: 1,
value3: 2
}
# Update the existing rows with the corresponding integer values
execute <<-SQL
UPDATE table_name SET new_column_name = #{enum_values[:value1]} WHERE column_name = 'value1';
UPDATE table_name SET new_column_name = #{enum_values[:value2]} WHERE column_name = 'value2';
UPDATE table_name SET new_column_name = #{enum_values[:value3]} WHERE column_name = 'value3';
SQL
# Define the new column as an enum
change_column :table_name, :new_column_name, :integer, using: 'new_column_name::integer'
add_index :table_name, :new_column_name
end
def down
remove_column :table_name, :new_column_name
end
end
Hope it helps!
