Crazy Multiplication Issue

"Dave Horn" <selurevad@yahoo.com> writes:

If I perform
select col1 * col2, 213.1086 * .833 from tableA
I get the following values
177.519464 and 177.583396

Works for me ... you *sure* it hasn't got something to do with the
datatypes you're using? Specifically, float4 is only good to about
six digits, so the bogus result is unsurprising if any of the values
involved are float4.

regards, tom lane

Howdy Gents-

Howdy to everyone else.

I am multiplying two columns it doesn't matter what their data type is
not does it seem to matter if I explicitly cast these values to some
other data type every time they are multiplied I am getting an
incorrect result.

col1 = 213.1086
col2 = 0.833

If I perform

select col1 * col2, 213.1086 * .833 from tableA

I get the following values

177.519464 and 177.583396

What I am doing wrong!?!??? I need to get the expected 177.583396
value. Thanks for any assistance!

I think the exact product is 177.5194638 (try SELECT 2131086 * 833), so
the column product =is= closer to correct than your "expected value".
Neither 213.1086 nor 0.833 is exactly representable in floating poit,
so you shouldn't expect any particular approximation, unless you're
sure of IEEE arithmetic, etc. Perhaps you want to use a NUMERIC type,
where you can declare the precision you require, e.g., NUMERIC(20, 10)
should do exact arithmetic to 10 decimal places (I think).

- John D. Burger
MITRE