CODEWITHSUNDEEP
ruby-on-railsmodel
blissini
blissini

Reputation: 545

Rails: change column type, but keep data

I have a model with a column of type integer which I want to convert to type string. Now I'm looking for the best way to change the column type without losing the data. Is there a painless way to accomplish this?

Upvotes: 31

Views: 21770

Answers (6)

Nesha Zoric
Nesha Zoric

Reputation: 6620

You can try something like this:

change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'

or even better:

change_column :table_name, :column_name, :integer, using: 'column_name::integer'

You can read more about this topic here: https://kolosek.com/rails-change-database-column

Upvotes: 2

Shajed
Shajed

Reputation: 667

for postgresql, change table column datatype integer to string,
rails migration like this with up and down actions 

class ChangeAgeToString < ActiveRecord::Migration
  def self.up  
    change_column :users, :age, 'varchar USING CAST(age AS varchar)', null: false
  end

  def self.down
    change_column :users, :age, 'integer USING CAST(age AS integer)', null: false, default: 0
  end
end

Upvotes: 5

Vincent
Vincent

Reputation: 2963

If you use Postgres, you can't implicitly cast a string back to an integer, so the way to make the change reversible is:

class ChangeAgeToString < ActiveRecord::Migration
  def self.up
    change_column :users, :age, :string
  end

  def self.down
    add_column :age_integer
    User.connection.execute('UPDATE users SET age_integer = cast(age as int)')
    remove_column :users, :age
    rename_column :users, :age_integer, :age
  end
end

Upvotes: 1

Денис Епишкин
Денис Епишкин

Reputation: 139

for postgres in migration

 change_column :table_name, :field,'boolean USING (CASE field WHEN \'your any string as true\' THEN \'t\'::boolean ELSE \'f\'::boolean END)'

and to any valid type similar

Upvotes: 5

Jon
Jon

Reputation: 10898

A standard migration using the change_column method will convert integers to strings without any data loss. rake db:rollback will also do the reverse migration without error if required.

Here is the test migration I used to confirm this behaviour:

class ChangeAgeToString < ActiveRecord::Migration
  def self.up
    change_column :users, :age, :string
  end

  def self.down
    change_column :users, :age, :integer
  end
end

Upvotes: 44

Kevin Hughes
Kevin Hughes

Reputation: 600

If it's a one-off you can just change the column type in the database (since no info is lost moving from int to varchar)

For MySQL, this would do it:

ALTER TABLE t1 MODIFY col1 VARCHAR(256)

If you're using SQLite you won't have to do anything.

Upvotes: 2

Related Questions