Re: Fw: [pgadmin-support] (Bug) Numeric fault calculation

Kanitchet Vaiassava <kanichet(at)hotmail(dot)com> wrote:

> [division and some math functions using the numeric type can
> sometimes have a result which the numeric type cannot represent
> exactly]

Yeah, you can use a simpler example:

test=# select '1'::numeric / '3'::numeric;
        ?column?        
------------------------
 0.33333333333333333333
(1 row)

test=# select '3'::numeric * ('1'::numeric / '3'::numeric);
        ?column?        
------------------------
 0.99999999999999999999
(1 row)

> So I think this problem should be solve? or at least, it should
> be note in document for other developer to be more careful.

Yeah, there should probably be something in the docs to indicate
that not all rational numbers (and certainly no irrational or
imaginary numbers) can be stored as a single numeric value without
loss of precision.

It might be interesting to create a "rational" type which would
internally hold two numeric values, and which would be capable of
doing what you want.  I'm not sure that the demand is sufficient to
back the development of it, though.  (Is there a convention for how
to indicate the repeating part of a decimal fraction when you can't
draw a line over those digits?)

In the absence of such a type, you might want to arrange your
calculations such that any division or square root calculations
are done last.  That way the effect of the truncation of the
repeating (or not) infinite decimal fraction won't be multiplied by
a later phase of the calculation.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company