Thread: (Bug) Numeric fault calculation

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