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
regression=# select '17637.75'::real, '17637.75'::real::numeric;
float4 | numeric
---------+---------
17637.8 | 17637.8
(1 row)
because the float4 output function rounds it off at the last trustworthy
digit. Your results suggest that you must be running with
extra_float_digits set to 1, which affects the behavior of the float4
output function ... but not that of float4->numeric conversion.
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 think, however, that you ought to spend some time contemplating
the fact that that extra digit is partially garbage. 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 --- so it might be better to do the standard
conversion and then go back and fix any values you can demonstrate
are wrong.
regards, tom lane
