Re: convert real to numeric. - Mailing list pgsql-general

On 2018-10-18 18:58:13 -0400, Tom Lane wrote:
> "Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> > On 2018-10-18 10:15:40 -0400, Tom Lane wrote:
> >> You could ju-jitsu the system into duplicating that behavior by casting
> >> to text (which invokes float4out) and then to numeric:
>
> > I suggest casting first to float8 and then to numeric. The conversion
> > from float4 to float8 is exact, and any rounding error introduced by the
> > float8->numeric conversion is certainly much smaller than the
> > uncertainty of the original float4 value.
>
> I do not think that the OP will care for the results of that.  The problem
> is that now the output function will think that the result is worth
> printing to 16 digits, and the last ten or so of those will be garbage.

You are thinking about "printing" (i.e., presentation to a user), I am
thinking about "converting" (i.e. storing the value as accurately as
possible in a different presentation). These are different things,

You should only think about "printing" when you actually print a value.
Depending on the inherent accuracy of the value and the purpose of the
display the best way to display the float4 value 17637.740234375 may be
'17637.740234375' (the exact value), '17637.74' (the minimum number of
decimal digits necessary to reconstruct the exact value), '17638' (we
don't care about fractions), '17,700' (three digits ought to be enough
for anybody), '17.7k' (the same, but more compact) or anything between.

It is for the application programmer to decide how to display a value,
because the programmer knows what it means, where it comes from and
what the user is supposed to do with that information. The runtime
environment doesn't know this. So it shouldn't throw away accuracy.
Often even parts of the application don't know this. So they shouldn't
either.


> As an example, even though the cited value happens to work nicely:
>
> regression=# select '17637.75'::float4::float8;
>   float8
> ----------
>  17637.75
> (1 row)
>
> nearby ones don't:
>
> regression=# select '17637.74'::float4::float8;
>      float8
> -----------------
>  17637.740234375
> (1 row)
>
> Yeah, in some sense that's a valid representation of the stored float4,

It is indeed the precise value which is stored (9030523 / 512).

> but it likely has little to do with the originally presented value.

It is much closer to the original value than 17637.7 (the relative
errors are about 1.33e-8 and 2.27e-6, so the error introduced by
PostgreSQLs default rounding is 170 times greater).

I think using FLT_DIG and DBL_DIG for converting from binary to decimal
is wrong. They represent the "number of decimal digits, q, such that any
floating-point number with q decimal digits can be rounded into a
floating-point number with p radix b digits and back again without
change to the q decimal digits" (ISO/IEC 9899:2011), which is not the
same as the number of decimal digits sufficient to convert a
binary to decimal and back again. Unfortunately, the latter is not a
constant and converting binary to decimal isn't trivial - nevertheless
the problem has been solved (at least for IEEE-754 arithmetic), so I
might take a stab at it (even with my limited numerical skills).

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>