Re: BUG #14138: Inconsistent rounding behavior in float4 coercion - Mailing list pgsql-bugs
From | Charles |
---|---|
Subject | Re: BUG #14138: Inconsistent rounding behavior in float4 coercion |
Date | |
Msg-id | CADBa3wbri2paoAasbD5m6p=ebPDSQPR=FARm7vWBup2-XfyJAQ@mail.gmail.com Whole thread Raw |
In response to | Re: BUG #14138: Inconsistent rounding behavior in float4 coercion (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: BUG #14138: Inconsistent rounding behavior in float4 coercion
Re: BUG #14138: Inconsistent rounding behavior in float4 coercion |
List | pgsql-bugs |
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@sss.pgh.pa.us> wrote: > 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 >
pgsql-bugs by date: