cooper.charles.m@gmail.com writes:
> Based on my reading of the documentation
> (http://www.postgresql.org/docs/9.5/static/datatype-numeric.html), float4
> only supports up to 6 decimal places of precision (even if the number fits
> comfortably in a 4-byte float). This is fine but I am finding it difficult
> to predict which way the float will truncate to:
I think you misunderstand floating-point arithmetic entirely. It's
rounding to the nearest so-many-digits binary value, which makes the
change in the last decimal digit hard to predict. The business about
"6 decimal digits" is only meant to indicate that you can expect that
many decimal digits to be reproduced reliably; whether and how digits
after the 6th change is a complicated business.
You can see more nearly what's going on by increasing extra_float_digits
so that more digits are printed. For example, at extra_float_digits = 3
it'll print a 9-decimal-digit rather than 6-decimal-digit approximation
to the underlying binary value, so:
regression=# select 15422.55 :: float4;
float4
------------
15422.5498
(1 row)
regression=# select 15422.5501 :: float4;
float4
------------
15422.5498
(1 row)
regression=# select 15422.5502 :: float4;
float4
------------
15422.5498
(1 row)
regression=# select 15422.5503 :: float4;
float4
------------
15422.5508
(1 row)
...
regression=# select 15422.5512 :: float4;
float4
------------
15422.5508
(1 row)
regression=# select 15422.5513 :: float4;
float4
------------
15422.5518
(1 row)
So adjacent binary values are about .001 apart in this range of values,
but they don't correspond exactly to multiples of .001. Near the
other value you cited:
regression=# select 11143.15 :: float4;
float4
------------
11143.1504
(1 row)
...
regression=# select 11143.1508 :: float4;
float4
------------
11143.1504
(1 row)
regression=# select 11143.1509 :: float4;
float4
------------
11143.1514
(1 row)
They're still about .001 apart, but now a little closer to the decimal
value below rather than the decimal value above, which accounts for the
different rounding behavior when showing fewer digits than this.
> Regardless of the default rounding mode (up / down / half even /
> you-name-it) I would expect these two to round in the same direction -- or
> at least the rounding algorithm to be specified and documented.
It's not really Postgres' business to try to document these rounding
behaviors, because as far as we're concerned they are platform-dependent.
Most modern machines follow IEEE 754,
https://en.wikipedia.org/wiki/IEEE_floating_point
but that's by no means universal and Postgres doesn't assume it.
If this is not something you're willing to deal with, use type numeric
rather than float4 or float8 --- numeric stores values in decimal notation
internally, so you don't get these odd roundoff effects from change of
base.
regards, tom lane