Answer a question

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.

Answers

What you do is an atomic operation, where first delete the column that you will not use. Then create the column of type decimal. Finally update the table and assign values. If you do not want to lose the information then you should support it through a SQL update procedure converting types. I hope you have served as an orientation. Greetings.

Lo que debes hacer es una operación atómica, donde primero elimines la columna que no vas a utilizar. Luego crear la columna de tipo decimal. Por último actualizar la tabla y asignarle valores. En caso de que no quieras perder la información deberías respaldarla para luego a través de un procedimiento SQL actualicen convirtiendo los tipos. Espero que te haya servido como una orientación. Saludos.

class ChangeOddsAndUnitsColumn < ActiveRecord::Migration
  def up
    change_table :bets do |t|
      t.decimal :units_placed, precision: 16, scale: 2
    end
    Bets.update_all ["units_placed = ?", 0.0]
  end

  def down
    remove_column :bets, :units_placed
  end
end