Ruby on Rails Migration Convert From String To Decimal

user_5968007 0
Viewed 1521

I've made a database and already inputted about 30-40 entries into the database but there was a mistake and I have accidentally set some columns of the database to be strings when I need them to be decimals in order to perform operations on them.

In order to change the database I have generated a migration file that includes this.

class ChangeOddsAndUnitsColumn < ActiveRecord::Migration
  def change
    change_column :bets, :units_placed, :decimal
    change_column :bets, :odds, :decimal
  end
end

However when I run rake db:migrate I get the following error

PG::DatatypeMismatch: ERROR:  column "units_placed" cannot be cast automatically to type numeric

The values in each column are already decimal numbers but I can't figure out how to convert them to the decimal type from a string.

postgresql ruby-on-rails ruby
2 Answers

In my case with Rails 6 this is what I did (TYPE CASTING)

class StringToDecimalConverter < ActiveRecord::Migration[6.0]
 def up
   change_column_default :table, :attr, nil
   change_column :table, :attr, "decimal USING CAST( attr AS decimal)"
   change_column :table, :attr, :decimal, precision: 21, scale: 3, default: 0.0    
 end

 def down
   change_column :table, :attr, :string, default: "0"
 end
end

In my case the reason for

change_column_default :table, :attr, nil

is that if you have a default value set it cannot be cast automatically, so you setting it to nil first solves this

The precision also cannot be set in the same change_column line

user_31975440
Related