Rails 4 migration to change columns data type from string to integer and back preserving data (postgres)

Question

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

This is 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 type of the fields like so:

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

And update model file.

/app/models/system.rb

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

How can I update old data?

PVG · Accepted Answer · 2016-08-31 12:52:28Z

After some research I found this to be a proper 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 default value prior to changing type. Here is the version with rollback.

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

General Failure · Accepted Answer · 2015-09-30 11:17:01Z

2

You can do it in 2 migration steps

1. Rename current operation column and add new with neccessary type

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

2. Move values from old column to new and drop 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

Don't forget write rollback code if it's neccessary

answered Sep 30, 2015 at 11:17

General Failure

2,6074 gold badges26 silver badges53 bronze badges

2 Comments

PVG Over a year ago

This migration will corrupt all data, because when you call .to_i on a string, you will always get 0. Next piece of code will work as expected. However it's not the best solution sys.operation = System.operations[sys.operation_str]

Ryan Murphy Over a year ago

Won't this remove the new column you just created rather than dropping the old column?

Collectives™ on Stack Overflow

Rails 4 migration to change columns data type from string to integer and back preserving data (postgres)

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related