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:
/Thescaleof anumericis 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_**_numericcan 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 onnumericvalues 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: Thescaleof anumericis the count of
decimal digits in the fractional part, to the right of the decimal
point. Theprecisionof anumericis 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 <mailto:yeren...@gmail.com>
*Sent:* Tuesday, April 23, 2013 3:48 PM
*To:* Kanitchet Vaiassava <mailto:kanic...@hotmail.com>
*Cc:* pgadmin-support@postgresql.org
<mailto:pgadmin-support@postgresql.org> ; Kanitchet Vaiassava
<mailto:kanitchet....@thaiace.co.th> ; Thanarit Maneechote
<mailto:thanarit....@thaiace.co.th>
*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
<mailto: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 <tel:%2B66%2089%20515%209955>; Office +66 2
744 2288 <tel:%2B66%202%20744%202288>; Fax +66 2 379 1166
---------------------------------------------------------------------------------------------------------------
--
Sent via pgadmin-support mailing list
(pgadmin-support@postgresql.org
<mailto:pgadmin-support@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
--
Regards,
Alexander Yerenkow
