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???
How many decimal places do you need to display? That number is there in scientific notation.
– rray
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
– Felipe Sachetti
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.
– Felipe Sachetti
I will try to change the fields to Numeric and test...
– Felipe Sachetti
You are using double to hold values that represent money?
– rray
yes, both money and percentages...
– Felipe Sachetti
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.
– rray
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
– Felipe Sachetti