CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
Thanh
Thanh

Reputation: 8604

How to rename a column and change its type by migration same time

In my general_exams table, I have a column named semester, type is string. Now I want to change its name to semester_id, type is integer. I have read about migration and it has available transformations:

So, I create my migration file like this:

class RenameSemesterFromGeneralExams < ActiveRecord::Migration

  def change
    rename_column :general_exams, :semester, :semester_id
    change_column :general_exams, :semester_id, :integer
  end
end

But, when I run rake db:migrate, it has error:

==  RenameSemesterFromGeneralExams: migrating =================================
-- rename_column(:general_exams, :semester, :semester_id)
   -> 0.0572s
-- change_column(:general_exams, :semester_id, :integer)
rake aborted!
An error has occurred, this and all later migrations canceled:

PG::Error: ERROR:  column "semester_id" cannot be cast to type integer
: ALTER TABLE "general_exams" ALTER COLUMN "semester_id" TYPE integer

In my table GeneralExam, I destroyed all data. So, anyone can tell me how can I do that? Or I must create two migration files?

Upvotes: 26

Views: 21604

Answers (5)

Elias Glyptis
Elias Glyptis

Reputation: 530

You can do this with one migration. But this does not always work and you may get an error, especially when converting a column from boolean type to integer type, or string type to integer type, unless you are specific how this data will be converted.

So you have a column in the general_exams table called semester and it's a string. You want to rename it to semester_id and you want to change it's type to integer.

Before you do any of this make sure you have your work committed to git, so you can always reset to your older commit and start fresh without loosing your work.

In your terminal run

rails g migration rename_the_column_semester_to_semester_id

This will generate a migration for you. Now open your migration file.

db/migrate/20210xxx_rename_the_column_semester_to_semester_id.rb

It will look like this:

class RenameTheColumnSemesterToSemesterID < ActiveRecord::Migration
  def change
  end
end

Inside the change method, make your file look like this.

class RenameTheColumnSemesterToSemesterID < ActiveRecord::Migration
  def change
    rename_column :general_exams, :semester, :semester_id
    change_column :general_exams, :semester_id, :integer, using: 'semester_id::integer'
  end
end

Alternatively you can do this:

class RenameTheColumnSemesterToSemesterID < ActiveRecord::Migration
  def change
    rename_column :general_exams, :semester, :semester_id
    change_column :general_exams, :semester_id, 'integer USING CAST(semester_id AS integer)'
  end
end

Note Important: The above will work only if your column does NOT have a default value in your table. This is important because if you've already set a default value to your column, you'll still get an error like PG::DatatypeMismatch: ERROR: default for column "semester_id" cannot be cast automatically to semester integer PG will complain that your semester column already has a default value. In this case, you will need to drop the default value after you rename the column and set a new default value.

class RenameTheColumnSemesterToSemesterID < ActiveRecord::Migration
  def change
    rename_column :general_exams, :semester, :semester_id
    execute "ALTER TABLE general_exams ALTER semester_id DROP DEFAULT;"
    change_column :general_exams, :semester_id, :integer, using: 'semester_id::integer', default: 1
  end
end

of-course you can set whatever default value you want, in case you are enumerating in your model from 0, 1, 2 or whatever.

Save your migration. and run

rails db:migrate

Upvotes: 0

James Dela Cruz
James Dela Cruz

Reputation: 106

I hope this help

class ModifyColumnTables 
  def change
    remove_column :posts, :old_column
    add_column :posts, :new_column, :type_of_column
  end
end

Upvotes: 1

wind
wind

Reputation: 440

This works as of Rails 4

def change
  rename_column :general_exams, :semester, :semester_id
  change_column :general_exams, :semester_id, :integer
end

Upvotes: 20

Tigraine
Tigraine

Reputation: 23648

Your problem is probably that the semester contains data that cannot be converted to integers. That's why you get a cast error.

I suspect you need to do more work to make this work as the only thing that comes to mind is removing the column and creating a new one with the correct values.

But you can simply remove_column and then add_column in one migration. That should work flawlessly.

I'd also suggest you only add_column first, then do the mapping process where you map the old semester value onto the new semester_id and then drop the column.

Keep in mind that you can do ActiveRecord manipulations inside your migration. So you can put that code in there.

Upvotes: 12

Mike Campbell
Mike Campbell

Reputation: 7978

This error is because there is existing data in the tables (or default values, perhaps ..) that PG doesn't know how to convert from string to integer. Either get rid of the data or tell PG how you want to convert it, using PG specific SQL (I think you'll want USING) and execute migration command. See Rails guides on migrations.

Upvotes: 1

Related Questions