Thread: BUG #5880: Rounding error when copying a REAL numeric value to DOUBLE PRECISION

The following bug has been logged online:

Bug reference:      5880
Logged by:          brad
Email address:      smithbs@gmail.com
PostgreSQL version: 8.4.5
Operating system:   linux x64
Description:        Rounding error when copying a REAL numeric value to
DOUBLE PRECISION
Details:

Steps to recreate are below.  The real value is improperly rounded when
inserted into a field that is double.  94.31 is stored as 94.3099975585938
when it is inserted into a double precision column.

CREATE TABLE tmpfloatToDouble
(field1 DOUBLE PRECISION,
field2 REAL);

INSERT INTO tmpfloatToDouble(field1, field2) VALUES(94.31, 94.31);
INSERT INTO tmpfloatToDouble(field1, field2) select field2, field1 FROM
tmpfloatToDouble;

SELECT * FROM tmpfloattodouble;

"brad" <smithbs@gmail.com> writes:
> Steps to recreate are below.  The real value is improperly rounded when
> inserted into a field that is double.  94.31 is stored as 94.3099975585938
> when it is inserted into a double precision column.

This is not a bug, it's just the typical behavior of float arithmetic.
"94.31" doesn't have any exact equivalent in float4, so when you convert
it to a form that is willing to print more decimal places, you find out
that indeed the stored value wasn't quite 94.31.

If this sort of thing makes you uncomfortable, probably you should be
using type "numeric" instead.

            regards, tom lane