Convert SQLite String to PostgreSQL

Question

I recently converted a string to a decimal in Rails using migrate and this works fine locally. I then pushed my application to heroku and got the following error:

Running `rake db:migrate` attached to terminal... up, run.9099
==  ChangePriceToDecimal: migrating ===========================================
-- change_column(:products, :price, :decimal, {:precision=>8, :scale=>2})
rake aborted!
An error has occurred, this and all later migrations canceled:

PG::Error: ERROR:  column "price" cannot be cast automatically to type numeric
HINT:  Specify a USING expression to perform the conversion.
: ALTER TABLE "products" ALTER COLUMN "price" TYPE decimal(8,2)

Here is the migrate file that I used:

class ChangePriceToDecimal < ActiveRecord::Migration
def change
change_column :products, :price, :decimal, precision: 8, scale: 2
end 
end

Any idea what I can do to resolve this?

Thanks, T

Answer 1

You have two possibilities:

1. Perform the transformation in Ruby. In this case, create another column such as temporary_price of desired format. With a rake task copy the data from the price to temporary_price converting it accordingly. Once completed, remove the price and rename temporary_price to price with another migration.

2. Perform the transformation using SQL. See the following answers/links:

   • RoR: Cannot change_column in postgres, fine in MySQL (MySQL for development, Postgres on Heroku)
   • https://dba.stackexchange.com/questions/6205/how-to-change-the-datatype-of-a-column-from-integer-to-money
   • http://guides.rubyonrails.org/migrations.html#using-the-up-down-methods