Heroku app and column data type

I ran into an error when I was pushing the last eager-loading app onto Heroku. I had the following error

2014-03-13T07:45:24.207959+00:00 app[web.1]: : SELECT products.*, categories.name as category_name FROM "products" INNER JOIN "categories" ON "categories"."id" = "products"."category_id"  ORDER BY categories.name):
2014-03-13T07:45:24.207959+00:00 app[web.1]:     13:     <% @products.each do |product| %>
2014-03-13T07:45:24.207959+00:00 app[web.1]:     14:       <tr>
2014-03-13T07:45:24.208979+00:00 app[web.1]:     15:         <td><%= link_to product.name, product %></td>
2014-03-13T07:45:24.208979+00:00 app[web.1]:     16:         <td><%= number_to_currency product.price %></td>
2014-03-13T07:45:24.208979+00:00 app[web.1]:   app/views/products/index.html.erb:13:in `_app_views_products_index_html_erb___1529240047445058566_70114821600640'
2014-03-13T07:45:24.208979+00:00 app[web.1]: 
2014-03-13T07:45:24.208979+00:00 app[web.1]: 
2014-03-13T07:45:24.208979+00:00 app[web.1]: I, [2014-03-13T07:45:24.200411 #2]  INFO -- : Started GET "/" for at 2014-03-13 07:45:24 +0000
2014-03-13T07:45:24.208979+00:00 app[web.1]: I, [2014-03-13T07:45:24.201343 #2]  INFO -- : Processing by ProductsController#index as HTML
2014-03-13T07:45:24.208979+00:00 app[web.1]: E, [2014-03-13T07:45:24.204799 #2] ERROR -- : PG::UndefinedFunction: ERROR:  operator does not exist: integer = character varying

After some searching around I found that my category_id on my products table was set to the datatype of string. My first attempt to fix this was to change the datatype of the category_id directly.

 class ChangeCategoryIdInProducts < ActiveRecord::Migration
   def self.up
     change_column :products, :category_id, :integer

   def self.down
     change_column :products, :category_id, :string

but this gave the error of

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

So directly changing the column does work, so I went with a stronger method. I removed the column category_id that was a string, and I added another column category_id that was an integer.

class ChangeCategoryIdForProducts < ActiveRecord::Migration
  def self.up
    remove_column :products, :category_id
    add_column :products, :category_id, :integer

  def self.down
    remove_column :products, :category_id
    add_column :products, :category_id, :string

This solved the problem I was having, but when I wanted to run rake db:reset Heroku gave me

FATAL:  permission denied for database "postgres"
DETAIL:  User does not have CONNECT privilege.

This is because you cannot drop a PG database on Heroku. The documentation mentions that you could run heroku pg:reset, but I was unable to get that working. I instead manually deleted the herokuapp, then re-uploaded the app onto heroku. When doing this, you have to remember to also remove the git remote the heroku toolbelt generated.

Rails: Remove a table

I created a table that I did not need. I needed to remove the table. The actual terminology is to Drop a table. The way you do this is to create a new migration.

rails g migration DropProducts

In my case, I created a products table when I didn’t needed it. Inside the migration, I do the following.

class DropProducts < ActiveRecord::Migration
  def change
    drop_table :products


Change the data type of a column in the database

I accidentally created a column in the database with the datatype of string when it needed to be a integer. I’m on the BART, so I didn’t have internet. Fortunately I have a copy of the Rails repo and its guides. I opened up migrations.md and read to the changing tables section. Saw the following:

change_table :products do |t|
  t.remove :description, :name
  t.string :part_number
  t.index :part_number
  t.rename :upccode, :upc_code

I followed this and created the following migration:

change_tabe :products do |t|
  t.remove :price_in_cents
  t.integer :price_in_cents