Cannot change column type during migrating to heroku

Question

This what i am getting when i am trying to run heroku run rake db:migrate

sender_id was a string

==  ChangeTypeToInteger: migrating ============================================
-- change_column(:messages, :sender_id, :integer)
rake aborted!
An error has occurred, this and all later migrations canceled:

PG::Error: ERROR:  column "sender_id" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion.
: ALTER TABLE "messages" ALTER COLUMN "sender_id" TYPE integer/app/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.12/lib/active_record/connection_adapters/postgresql_adapter.rb:652:in `exec'

This appears to be a dupe of this question: http://stackoverflow.com/questions/12603498/rails-migration-error-w-postgres-when-pushing-to-heroku — davidcelis, Sep 20 '13 at 00:10
my development db is sqlite , i think i will get errors if i used this in the migration file on sqlite3 — Mostafa Hussein, Sep 20 '13 at 00:12
I would highly recommend against using different databases in your different environments precisely for reasons like this — davidcelis, Sep 20 '13 at 00:14

score 0 · Accepted Answer · answered Sep 20 '13 at 00:18

I quote the manual about ALTER TABLE:

A USING clause must be provided if there is no implicit or assignment cast from old to new type.

What you need is:

ALTER TABLE messages ALTER COLUMN sender_id TYPE integer

This works with or without data as long as all entries are convertible to integer.
If you have defined a DEFAULT for the column, you may have to drop and recreate that for the new type.

Here is blog article on how to do this with ActiveRecord.

Cannot change column type during migrating to heroku

1 Answers1