Thread: (Bug) Numeric fault calculation

(Bug) Numeric fault calculation

From
"Kanitchet Vaiassava"
Date:
(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
---------------------------------------------------------------------------------------------------------------
Attachment

Re: (Bug) Numeric fault calculation

From
Alexander Yerenkow
Date:
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

Re: (Bug) Numeric fault calculation

From
Chirag Mittal
Date:
Dear Kanitchet,

Please try this 

SELECT (260739.94 * (1.00/365.00))::numeric(20,7)

I am a beginner but I think we need to explicitly mention the "output"
format required to get accuracy.

Hope it helped.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Bug-Numeric-fault-calculation-tp5752984p5759375.html
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.



Re: (Bug) Numeric fault calculation

From
Guillaume Lelarge
Date:
On Sun, 2013-06-16 at 04:59 -0700, Chirag Mittal wrote:
> Dear Kanitchet,
> 
> Please try this 
> 
> SELECT (260739.94 * (1.00/365.00))::numeric(20,7)
> 
> I am a beginner but I think we need to explicitly mention the "output"
> format required to get accuracy.
> 

Nope, you simply need to use the right datatype. If you use a floating
point datatype (which is what happens when you don't specificy the
type), you cannot claim to get exact precision. If you need the
accuracy, you need the numeric datatype (just as you did with the query
above).


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com




Re: (Bug) Numeric fault calculation

From
David Johnston
Date:
Guillaume Lelarge-3 wrote
> On Sun, 2013-06-16 at 04:59 -0700, Chirag Mittal wrote:
>> Dear Kanitchet,
>> 
>> Please try this 
>> 
>> SELECT (260739.94 * (1.00/365.00))::numeric(20,7)
>> 
>> I am a beginner but I think we need to explicitly mention the "output"
>> format required to get accuracy.
>> 
> 
> Nope, you simply need to use the right datatype. If you use a floating
> point datatype (which is what happens when you don't specificy the
> type), you cannot claim to get exact precision. If you need the
> accuracy, you need the numeric datatype (just as you did with the query
> above).

I think the OP is on to something:

SELECT (260739.94 * (1.00/365.00))::text,  --714.35599999999...      (260739.94 * (1.00/365.00))::numeric(20,7)::text,
--714.3560000     (260739.94::numeric * (1.00::numeric/365.00::numeric))::text,
 
--714.355999999999....      (260739.94::double precision * (1.00::double precision/365.00::double
precision))::text -- 714.356

Unadorned decimal numbers seem to be converted to "numeric" by default since
both the first and third calculation result in the same output.  By
constraining the numeric to (20,7) [post operation] rounding occurs and you
get the second calculation.  

The fourth calculation explicitly treats the number inputs as double
precision and the calculation comes out to exactly 714.356.  The supplied
long division indicates that the correct answer is exactly 714.356.   In
this case the numeric datatype, which supposedly is more precise/accurate
than double precision, is giving an incorrect answer.

Note that using "real" instead of "double precision" gives the same exact
result.

9.0 on Ubuntu 10.04

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Bug-Numeric-fault-calculation-tp5752984p5759766.html
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.



Re: (Bug) Numeric fault calculation

From
David Johnston
Date:
Guillaume Lelarge-3 wrote
> If you use a floating
> point datatype (which is what happens when you don't specificy the
> type), 

I covered this in my prior post but per the documentation this is incorrect.  

http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

4.1.2.6. Numeric Constants

"Constants that contain decimal points and/or exponents are always initially
presumed to be type numeric."

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Bug-Numeric-fault-calculation-tp5752984p5759768.html
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.



Re: (Bug) Numeric fault calculation

From
David Johnston
Date:
Kanitchet Vaiassava wrote
> (Bug) Numeric fault calculation
> 
> postgresql : 714.35599999999xxxx
> long division method: 714.356

FYI:

My prior posts use a result cast "::text" in order to avoid any kind of
interaction with how pgAdmin would handle numbers.  This is not a pgadmin
bug/issue it is in the database proper.

If you experience the error in pgadmin you should endeavor to make sure
whether it is a pgadmin problem or PostgreSQL in general.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Bug-Numeric-fault-calculation-tp5752984p5759769.html
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.



Re: (Bug) Numeric fault calculation

From
Guillaume Lelarge
Date:
On Tue, 2013-06-18 at 15:30 -0700, David Johnston wrote:
> Guillaume Lelarge-3 wrote
> > If you use a floating
> > point datatype (which is what happens when you don't specificy the
> > type), 
> 
> I covered this in my prior post but per the documentation this is incorrect.  
> 
> http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
> 
> 4.1.2.6. Numeric Constants
> 
> "Constants that contain decimal points and/or exponents are always initially
> presumed to be type numeric."
> 

postgres=# select pg_typeof(260739.94);pg_typeof 
-----------numeric
(1 row)

Seems you're right. Sorry.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com