Re: BUG #14138: Inconsistent rounding behavior in float4 coercion

Thanks for taking the time to show me how to print those extra digits of
precision, it really helped!

As a follow-up question, I am a bit confused as to the following behavior:

elsendb=> set extra_float_digits = 3;
SET
elsendb=> select 11143.15 :: float4;
float4
------------
11143.1504
(1 row)

elsendb=> select 11143.15 :: float4 :: text :: float4;
float4
------------
11143.1504
(1 row)

elsendb=> select 11143.15 :: float4 :: numeric :: float4;
float4
------------
11143.2002
(1 row)

How come 11143.1504 is not printed in all of these cases?

Best,
Charles

On Sun, May 15, 2016 at 1:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> cooper(dot)charles(dot)m(at)gmail(dot)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
>