Geek Answers Handbook

Rails 4 migrations to change the column data type from string data to integer and backward-saving data (postgres)

I need to convert string fields to integer and use enum instead. What is the best way to do this without data loss?

This is the current migration:

 class CreateSystems < ActiveRecord::Migration def change create_table :systems do |t| t.string :operation t.string :status t.timestamps null: false end end end

Then I change the type of the fields as follows:

 class ChangeColumnsForSystems < ActiveRecord::Migration def change change_column :systems, :operation, :integer change_column :systems, :status, :integer end end

And update the model file.

/app/models/system.rb

 ... enum operation { start: 0, stop: 1 } enum status { init: 0, working: 1, complete: 2 } ...

How to update old data?

+7
enums ruby-on-rails rails-migrations
source share
2 answers

After some research, I found this a suitable solution.

 class ChangeColumnsForSystems < ActiveRecord::Migration def change change_column :systems, :operation, "integer USING (CASE operation WHEN 'start' THEN '0'::integer ELSE '1'::integer END)", null: false change_column :systems, :status, "integer USING (CASE status WHEN 'init' THEN '0'::integer WHEN 'working' THEN '1'::integer ELSE '2'::integer END)", null: false end end

UPDATE: In some cases, you will have to remove the default value before changing the type. Here is the rollback version.

 class ChangeColumnsForSystems < ActiveRecord::Migration def up change_column_default :systems, :status, nil change_column :systems, :operation, "integer USING (CASE operation WHEN 'start' THEN '0'::integer ELSE '1'::integer END)", null: false change_column :systems, :status, "integer USING (CASE status WHEN 'init' THEN '0'::integer WHEN 'working' THEN '1'::integer ELSE '2'::integer END)", null: false, default: 0 end def down change_column_default :systems, :status, nil change_column :systems, :operation, "varchar USING (CASE operation WHEN '0' THEN 'start'::varchar ELSE 'stop'::varchar END)", null: false change_column :systems, :status, "varchar USING (CASE status WHEN '0' THEN 'init'::varchar WHEN '1' THEN 'working'::varchar ELSE 'complete'::varchar END)", null: false, default: 'init' end end
+7
source share

You can do this in two stages of migration.

1. Rename the current operation column and add a new one with the required type

 def up rename_column :systems, :operation, :operation_str add_column :systems, :operation, ... # your options end

2. Move values ​​from the old column to the new one and delete the old column

 def up System.all.each do |sys| sys.operation = sys.operation_str.to_i # replace it with your converter end remove_column :systems, :operation end

Do not forget to write the rollback code, if necessary

+2
source share

More articles:


All Articles