On 2018-10-18 10:15:40 -0400, Tom Lane wrote:
> Alessandro Aste <alessandro.aste@gmail.com> writes:
> > I need to convert an SQL field from real to numeric, but I’m getting a
> > strange behavior.
> > select amount, amount::numeric, amount::numeric(16,4),
> > amount::varchar::numeric from mytable where id = 32560545;
> > Result:
> > 17637.75, 17637.8, 17637.8000, 17637.75
>
> You realize of course that "real" (a/k/a float4) can only be trusted
> to six decimal digits in the first place. When I try this, I get
24 bits, actually. Using decimal digits when talking about binary
numbers is misleading.
[...]
> You could ju-jitsu the system into duplicating that behavior by casting
> to text (which invokes float4out) and then to numeric:
>
> regression=# set extra_float_digits to 1;
> SET
> regression=# select '17637.75'::real, '17637.75'::real::numeric;
> float4 | numeric
> ----------+---------
> 17637.75 | 17637.8
> (1 row)
>
> regression=# select '17637.75'::real, '17637.75'::real::text::numeric;
> float4 | numeric
> ----------+----------
> 17637.75 | 17637.75
> (1 row)
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 think, however, that you ought to spend some time contemplating
> the fact that that extra digit is partially garbage.
If we assume that 17637.75 was the result of rounding a more precise
value to a float4, then the real value was somewhere between
17637.7490234375 and 17637.7509765625. Rounding to 17637.8 introduces an
error almost 50 times larger.
> I'm not really convinced that doing it like this rather than doing the
> standard conversion is a good idea. You can't manufacture precision
> where there is none
It may be that the real value of that number is only known to +/- 0.1.
Or maybe only to +/- 100. But postgresql can't know that, and
gratuitously adding additional rounding errors doesn't help.
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/>
