PostgreSQL Rails migration – how to fix DatatypeMismatch error

So recently I made my very first blog app (you can watch this awful creation here) and while uploading to Heroku was as expected stress free, migrations weren’t.

The error was

[readolog_blockquote ]

PG::DatatypeMismatch: ERROR:  column “category_id” cannot be cast automatically to type integer

HINT:  Specify a USING expression to perform the conversion.

[/readolog_blockquote]

I could imagine this would cause a problem, because initially “category_id” was named plain “category” and was a string instead of an integer.

By simply creating a new migration

class FixColumnName < ActiveRecord::Migration
  def change
  	rename_column :posts, :category, :category_id
  	change_column :posts, :category_id, :integer
  end
end

I thought to have it done until I stumbled upon the error.

My first search led me to this awesome article – check it out http://makandracards.com/makandra/18691-postgresql-vs-rails-migration-how-to-change-columns-from-string-to-integer

(You basically need to follow the HINT and convert your data as following:

class FixColumnName < ActiveRecord::Migration
  def change
  	rename_column :posts, :category, :category_id
  	change_column :posts, :category_id, 'integer USING CAST(category_id AS integer)'
  end
end

However, after adding a new migration… the error still occured. Until I watched carefully the error logs

== 20150520191141 FixColumnName: migrating ====================================
-- rename_column(:posts, :category, :category_id)
   -> 0.0029s
-- change_column(:posts, :category_id, :integer)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::DatatypeMismatch: ERROR:  column "category_id" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion.

As you can see, not the new migration is causing problems, but the old one! So if you encounter the same problem as I did, please check your older migrations – you just need to change this line in the old migration as well:

change_column :posts, :category_id, 'integer USING CAST(category_id AS integer)'