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

Dear,

I had forwarded my bug report from pgadmin-support to pgsql-bugs (with attach file) and sorry for send it to pgadmin-support
and please allows me to quote more form pg’s document and do more underline
http://www.postgresql.org/docs/9.1/static/datatype-numeric.html
8.1.3. Floating-Point Types
The data types real and double precision are inexact, variable-precision numeric types. In practice, these types are usually implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.

Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed here, except for the following points:

a.. If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.

b.. If you want to do complicated calculations with these types for anything important, especially if you rely on certain behavior in boundary cases (infinity, underflow), you should evaluate the implementation carefully.

c.. Comparing two floating-point values for equality might not always work as expected.

Please fix this, and if you have any suggestion to workaround right now it will be great.

sorry for bad english.

Thank you.

Best Regards,
Kanitchet Vaiassava

ThaiAce Capital Co., Ltd
999 Nawamin Rd., Nuanjun, Buengkum, Bangkok 10230, Thailand
Mobile +66 89 515 9955; Office +66 2 944 2000 Ext.1204; Fax +66 2 944 2020
---------------------------------------------------------------------------------------------------------------

From: Michal Kozusznik
Sent: Tuesday, April 23, 2013 5:06 PM
To: pgadmin-support(at)postgresql(dot)org
Subject: Re: Fw: [pgadmin-support] (Bug) Numeric fault calculation

Intresting thing. I never thought it is so loose.
On the other side, I would never use double for currency calculations. It provides instability to results due to floating point precision.

However due to this:

The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point

Try to this:
SELECT (260739.94 * (1.00000000000000000000000000000/365))
_____________________
714.3560000000000000000000000714356

With regards

On 23.4.2013 11:17, Kanitchet Vaiassava wrote:

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.

From: Alexander Yerenkow
Sent: Tuesday, April 23, 2013 3:48 PM
To: Kanitchet Vaiassava
Cc: pgadmin-support(at)postgresql(dot)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 <kanichet(at)hotmail(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

--
Regards,
Alexander Yerenkow