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.