Postgres Pro
Downloads
Home   >   mailing lists

Fw: (Bug) Numeric fault calculation - Mailing list pgadmin-support

From Kanitchet Vaiassava
Subject Fw: (Bug) Numeric fault calculation
Date
Msg-id BAY167-DS3285FF28CABDFCF5126BE2D8B40@phx.gbl
Whole thread Raw
Responses Re: Fw: (Bug) Numeric fault calculation
List pgadmin-support
Tree view

Dear pg's support and Alexander

 

Allow me to quote this reference from : http://www.postgresql.org/docs/9.1/static/datatype-numeric.html

"The type numeric can store numbers with a very large number of digits and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required. However, arithmetic on numeric values is very slow compared to the integer types, or to the floating-point types described in the next section.

We use the following terms below: The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero."

 
Thai is the documentation that has been show right now. It's mean that others developer may using this recommended "numeric" in financial and accounting which is mission critical. right?
 
So I think this problem should be solve? or at least, it should be note in document for other developer to be more careful.
 
 
Best Regards,
Kanitchet Vaiassava
ThaiAce Group
555 Nawamin Rd., Klongkum, Buengkum, Bangkok 10230, Thailand
Mobile +66 89 515 9955; Office +66 2 744 2288; Fax +66 2 379 1166
---------------------------------------------------------------------------------------------------------------

Sent: Tuesday, April 23, 2013 3:48 PM
Subject: Re: [pgadmin-support] (Bug) Numeric fault calculation

Try

select (260739.94::double precision * (1.00::double precision / 365.00::double precision) )

default precision in postgres is pretty lossy, use double precision whenever you need max precision.




2013/4/23 Kanitchet Vaiassava <kanichet@hotmail.com>
(Bug) Numeric fault calculation
 
My company has using postgresql as database for ERP application which in-house developed.
For store financial and accounting data, we chose "numeric" type for accurate calculation (and with recommend by postgres's documentation) and we faced the problem by using "double precision" before.
 
However, we found that by using numeric had the problem too.
In our formula for calculate interest for customer's overdue payment that using numeric,
we found that it had fault calculate. So, it effected our interest amount.
 
In the attached file you can see that the result from postgresql and by using long division method is difference.
 
postgresql : 714.35599999999xxxx
long division method: 714.356
 
and if we multiply this result with interest rate and others factor and round up later. the amount is miscalculate.
 
Thank you and sorry for bad english gramma.
 
 
Best Regards,
Kanitchet Vaiassava
ThaiAce Group
555 Nawamin Rd., Klongkum, Buengkum, Bangkok 10230, Thailand
Mobile +66 89 515 9955; Office +66 2 744 2288; Fax +66 2 379 1166
---------------------------------------------------------------------------------------------------------------


--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support




--
Regards,
Alexander Yerenkow

pgadmin-support by date:

Previous
From: Alexander Yerenkow
Date:
Subject: Re: (Bug) Numeric fault calculation
Next
From: Michal Kozusznik
Date:
Subject: Re: Fw: (Bug) Numeric fault calculation