BigDecimal comparison value when decimals

user_11548857 0
Viewed 35

I'm storing data with a numeric type in a postgresql database

CREATE TABLE public.subscriptions (
    amount numeric(15,2)
)

But when I use this amount, in a BigDecimal format, to compare it to its own value, in a float format, it fails in some cases. Unless I multiply it to avoid the decimals:

subscription.amount == 521.18 #=> false
subscription.amount * 100 == 52118 #=> true

It seems that the BigDecimal format is the issue here (but I'm not sure it's exactly the same case)

BigDecimal(52118)/100 == 521.18 #=> false
BigDecimal(52117)/100 == 521.17 #=> true

BigDecimal(52118) == 52118 #=> true
BigDecimal(52117) == 52117 #=> true

Do you know what is the explanation of this ? Should I then avoid comparing BigDecimal numbers with float numbers containing decimals ?

Many thanks for your help :)

postgresql ruby-on-rails
1 Answers

As mentioned in the comments, this is an issue with the float, not the decimal:

irb(main):009:0> 100 * 521.18 == 52118
=> false
irb(main):010:0> 100 * 521.18
=> 52117.99999999999

Float imprecision is the whole reason BigDecimals exist. Converting the float before the comparison will work too:

irb(main):014:0> "521.18".to_d == 521.18.to_d
=> true
user_1527860
Related