Thread: (Bug) Numeric fault calculation
(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
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
---------------------------------------------------------------------------------------------------------------
555 Nawamin Rd., Klongkum, Buengkum, Bangkok 10230, Thailand
Mobile +66 89 515 9955; Office +66 2 744 2288; Fax +66 2 379 1166
---------------------------------------------------------------------------------------------------------------
Attachment
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 calculationMy 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.35599999999xxxxlong division method: 714.356and 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 VaiassavaThaiAce 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
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.
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
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.
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.
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.
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