Alessandro Aste <alessandro.aste@xxxxxxxxx> 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
