Saving a BigDecimal to a decimal field in the database makes BigDecimal different

Question

I always thought you should never use floats for saving money in the database. So i used decimal. The problem is i save a decimal 100 and it puts it in the database as 99.9999

I have a model named GameCommission

Migration:

create_table :game_commissions, :force => true do |t|
  t.integer :game_id
  t.integer :widget_id
  t.integer :user_id
  t.decimal :amount, :precision => 6, :scale => 4, :default => 0
  t.date :end_date
  t.timestamps
end

Now i make quickly a new GameCommission:

amount = BigDecimal.new("100")
gc = GameCommission.new(:game_id => 1, :widget_id => 1, :user_id => User.last.id, :amount => amount, :end_date => Date.today)
gc.save
gc.amount.to_s
# => "100

the amount is now 100 which is what i want. But if i fetch it again the amount changes to 99.9999 and that is also the amount that is saved in the database....

gc = GameCommission.last
gc.amount.to_s
# => "99.9999"

Somebody knows what is going on?

Answer 1

You're using the values :precision => 6, :scale => 4 in your :amount field, causing 100 to be scaled down to 99.9999 as the 3 figures of 100 plus the 4 mandatory decimal places = 7, which is greater than 6.

:precision => 6, :scale => 4 can store a number from -99.9999 to 99.9999, which is why 100 is being downscaled.

Change :precision to 7 and you should be good to go.