Subject Re: rounding error in computed expression in query
Author Adam
> Thnax for reply,
> I tested this on my machine (on FB service) - and results are as
> were described (and select returns strange non-rounded nummbers like
> 154,5899963378906 even
> for simple select f1 from fact_table_float (when field is defined as
> float and even in the case when 154.5900000 is inserted))... Well as
> I am using double precision fields in many places then it is hard to
> decide what to do... So far it was worked mostly.


Double Precision is just an expanded version of float. By definition
it is not capable of storing exact numbers. The problem here is that
many people do not understand the subtle difference between exactness
and precision, and the fact that you can have one precision without
exactness. Precision is talking about how many decimal places you have
to play with. Exactness is talking about how many of those decimal
places are guaranteed. Floats and Double Precisions are good for
storing approximations due to their range, but bad for storing exact
measurements (like money) because their lack of exactness will bite
you. It may be OK for a while, because rounding for the UI may hide
the inexactness, but introduce a few arithmetic operations and you
soon see the difference.

My advice is to change the fields to a type suitable for storing the
data you want to store. Clumsy workarounds using runtime casting to
numerics will bite you later.