How do I change column type in Heroku?

Question

I am trying to rake the db:migrations into my heorku instance and I get an error. The FAQ described my error as below:

Cannot change column type

Example: PGError: ERROR: column “verified_at” cannot be cast to type “date”

Cause: PostgreSQL doesn’t know how to cast all the rows in that table to the specified type. Most likely it means you have an integer or a string in that column.

Solution: Inspect your records and make sure they can be converted to the new type. Sometimes it’s easier to just avoid using change_column, renaming/creating a new column instead.

How do I change this migration now. This is the problem that I have. For my Contacts table, I created the following:

  t.string :date_entered

In a later migration, I do the following:

 change_column :contacts, :date_entered, :date

This change_column appears to be the problem.

Should I...change by hand that migration? Is there a way I can clean the data in my tables (I didn't know Heroku would recognize the data in the table because I'm doing a rake).

I obviously need to change this value and it is used throughout my application. Thanks.

This is what I am trying...thoughts?

def self.up
  #change_column :contacts, :date_entered, :date
  #this fails in postgres, so trying the same outcome 

  rename_column :contacts, :date_entered, :date_entered_old
  add_column :contacts, :date_entered, :date
  remove_column :contacts, :date_entered_old
end

def self.down
  add_column :contacts, :date_entered_old
  remove_column :contacts, :date_entered
  rename_column :contacts, :date_entered_old, :date_entered
end

Answer 1

Do the following:

1. rename the column A
2. create the new column B as date
3. move the data from A to B
4. remove A

In other words

def self.up   rename_column :contacts, :date_entered, :date_entered_string   add_column :contacts, :date_entered, :date    Contact.reset_column_information   Contact.find_each { |c| c.update_attribute(:date_entered, c.date_entered_string) }    remove_column :contacts, :date_entered_string end 

Answer 2

This is a modified and tested version of Simone Carletti's solution

class ModifyContacts < ActiveRecord::Migration   def self.up     rename_column :contacts, :date_entered, :date_entered_string     add_column :contacts, :date_entered, :date      Contact.reset_column_information     Contact.find(:all).each { |contact| contact.update_attribute(:date_entered, contact.date_entered_string) }     remove_column :contacts, :date_entered_string   end end