views:

157

answers:

2

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
+3  A: 

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
  create_column :contacts, :date_entered, :date

  Contact.reset_column_information
  Contact.find_each { |c| c.update_attribute(:date_entered, c.date_entered_string) } 
  delete_column :contacts, :date_entered_string
end
Simone Carletti
+1 for #reset_column_information, which I had never seen before. That looks like it would be handy in those very rare times when it's needed.
jdl
ditto re: reset.
Angela
A: 

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
Arda Basoglu