Wrote this post because I couldn't find an article in the World Wide Web on how to change the column type and convert its data.✝
So let's say we have a Record model with boolean column master, where a true
values means that it's a master release and false
means that it's not. Records that aren't master releases can either be re-issues, special editions, remixes. We decided to model this info using just one column, and store it as string
s.
Active Record's migration method change_column
allows us to change a column's name and type. However it doesn't provide a way to also do data conversion.☦ My suggestion to implement this has three steps:
- Add the column — release_type — with the new data type;
- Convert boolean values from master column into respective string in release_type column;
- Remove master column.
The migration code looks like this:
def up
add_column :records, :release_type, :string
cast_values = <<-SQL
UPDATE records
SET release_type = 'Re-Issue'
WHERE master = FALSE;
UPDATE records
SET release_type = 'Master Release'
WHERE master = TRUE;
SQL
ActiveRecord::Base.connection.execute(cast_values)
remove_column :records, :master
end
The down
method would reverse this logic and it's left for the reader as exercise.
✝ Must confess that I bumped into many articles changing integer
columns into text
, or datetime
into date
. Although data conversion is implied it is something that Rails can handle on its own.
☦ This article does suggest that one can provide a SQL statement for data conversion. However I couldn't find any official documentation to support that.
Top comments (0)