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 --------------------------------------------------------------------------------------------------------------- From: Alexander Yerenkow Sent: Tuesday, April 23, 2013 3:48 PM To: Kanitchet Vaiassava Cc: pgadmin-support@postgresql.org ; Kanitchet Vaiassava ; Thanarit Maneechote 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 <kanic...@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
