Subtraction return on postgres

Asked

Viewed 496 times

1

I have a table with a field called VALUE double Precision, and a function that returns another data also double Precision, what happens is that if I do

select (VALUE - function()) the results in which case both the VALUE and the result of the function are equal, the result is 2.27373675443232e-013

I found it very strange and I’m not knowing what to do, I’ve performed some tests with conversions and rounding and nothing

Does anyone have any idea what might be going on???

postgresql
  • 1

    How many decimal places do you need to display? That number is there in scientific notation.

  • I only need two... However the two, both the column value is with value of 1609.86 and the return of the function is being 1609.86 so the result would be 0.0 and not this NR in scientific notation

  • I got a reply in the stack in English double Precision is a lossy data type. The decimal representation you see is only an approximation of the Internal Binary value. The Kind of "Unexpected" Mismatch you see is to be expected when calculating with floating-point Numbers. You could work with a Loss-Less type like Numeric to avoid this. (And possibly round to a Reasonable number of fractional digits.) Start by Reading the Postgres manual on Numeric data types and at least one of the Many Articles out there about floating-point arithmetic.

  • I will try to change the fields to Numeric and test...

  • You are using double to hold values that represent money?

  • yes, both money and percentages...

  • 1

    Double has even more rounding problems if it is to represent money, there are times when the pennies simply change. Ideal is to switch to Numeric or bigint. here and that other answer has more details about the double/float/real problem with monetary values.

  • I realized a new question, related to changing my fields, I have some tables already and would like to change all columns at once, is there the possibility?? http://answall.com/questions/103349/alterar-tipo-campo-de-todas-as-tabelas

Show 3 more comments
No answers

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.