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

Question

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:

• rename_column(table_name, column_name, new_column_name): Renames a column but keeps the type and content.
• change_column(table_name, column_name, type, options): Changes the column to a different type using the same parameters as add_column.

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?

Solution 1

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.

Solution 2

This works as of Rails 4

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

Solution 3

I hope this help

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

Comments

[EdgeCaseBerg]

This question is actually really helpful because, besides the error in it, it specifies exactly how to change a column name and type in the question itself!

[Thanh]

I destroyed all data in table GeneralExam. If I find no way to do that same time, I will create 2 migration, but I think it's not convenient. Thanks for answer.

[Tigraine]

Updated my answer with some more details

[Thanh]

I destroyed all data in that table. You mean I should using up and down?

[Thanh]

I don't need old data of semester column. So remove_column or add_column first maybe not a problem. I will do this way, thanks.

[Mike Campbell]

If you've destroyed all the data, then the only thing I can think of is that the column might have a default value (which can't be converted to integer). I'm not a PG expect.

[Thanh]

I don't set default value for that column, I'm also not understand why I can not change type when column has not value. So I had to choose the other way. Lol, I'm also not any expert, I'm still only student :))