0

I changed a column in my database like so:

class ChangePriceToBeFloatInProducts < ActiveRecord::Migration[5.1]
  def change
      change_column :products, :price, :float
  end
end

It works fine on my local machine, but whenever I try to run this migration on Heroku via heroku run rake db:migrate I get the following error: 

StandardError: An error has occurred, this and all later migrations canceled:

    PG::DatatypeMismatch: ERROR:  column "price" cannot be cast automatically to type double precision
    HINT:  You might need to specify "USING price::double precision".
    : ALTER TABLE "products" ALTER COLUMN "price" TYPE float

I've already tried to reset my database, but I'm still getting this same error.

How can I fix this?

ChrisGPT was on strike
  • 127,765
  • 105
  • 273
  • 257
Andreia
  • 167
  • 1
  • 2
  • 9
  • That error looks like it's coming from PostgreSQL. Are you also using a PostgreSQL database on your development machine, or are you using something else (e.g. SQLite, MariaDB, etc.)? – ChrisGPT was on strike Apr 29 '18 at 15:24
  • @Chris I'm still a newbie here, but I checked on my terminal and apparently, I am using MySQL – Andreia Apr 29 '18 at 15:37
  • It looks like you're using PostgreSQL on Heroku, which is the default. You should consider installing it locally as well, do your development environment more closely matches production. If you're on OSX, https://postgresapp.com/ is a very easy way to get going on Postrges. – user229044 Apr 29 '18 at 15:52

3 Answers3

2

Your error message is coming from PostgreSQL but you are developing using MySQL.

It's a very good idea to use the same database technology in development and production (as well as testing, staging, and any other environments you may have). This will minimize surprises when you move from one environment to the next, e.g. when you deploy your code to Heroku.

I recommend one of the following two courses of action:

  • Switch to PostgreSQL on your development machine.

    This will probably cause your migration to fail locally, but that's a good thing! Now you can troubleshoot and fix the error in development, which is much better than dealing with issues that pop up in production.

    In this case the problem is linked to the current type of your price column. PostgreSQL isn't able to convert that data type to a float by itself and needs some help from you.

  • Switch to MySQL on Heroku.

    Heroku uses PostgreSQL out of the box, but it also supports many other data stores. Pick one of the MySQL providers and use it instead of PostgreSQL.

In both cases, try to match the version number of the database provider.

Either of these will work, but my strong preference would be the first option. MySQL is a bit too fast and loose with data types for my liking. If you choose to go with MySQL take a close look at your data before and after running your migration locally to make sure it is doing "the right thing".

Edit: It looks like you're actually using SQLite on your local machine. That won't work on Heroku due to its ephemeral filesystem. You'll have to use a client-server database on Heroku, and I strongly recommend using the same one on your local development machine.

ChrisGPT was on strike
  • 127,765
  • 105
  • 273
  • 257
  • I was just double checking this all databases thing, and I am using SQLite after all. Sorry, I'm still getting familiar with these things. Should I switch this to PostgreSQL still? – Andreia Apr 29 '18 at 16:31
  • @Andreia, I just added a note about SQLite to my answer. In short, it won't work on Heroku. Switching to PostgreSQL on your development machine is a good option. – ChrisGPT was on strike Apr 29 '18 at 16:35
  • Thank you for your response. Did that and everything is working now – Andreia May 01 '18 at 15:54
1

I believe if you want to change that column type Postgres wants you to tell it what to do with any values currently in the column. The hint is pointing you to:

change_column :products, :price, 'float USING CAST(price AS float)'

If the Rails adapter for Postgres doesn't like 'float' you might have to change to wording of the type.

Beartech
  • 6,173
  • 1
  • 18
  • 41
  • https://jdc.io/rails-migration-data-types-mysql-postgresql-sqlite here's a table that appears to layout how the different DB adapters types relate to Rails. But as mentioned in the other answer you probably want to match your DBs to save pain and anguish in the long run. – Beartech Apr 29 '18 at 15:49
0

If you're on development environment, I'd drop the column:

remove_column :products, :price

And adding it again:

add_column :products, :price, :float

Then running migration should pass without any problems

Unless you have data on it then you'll have to change using expressions

Amir El-Bashary
  • 388
  • 3
  • 13