convert real to numeric.

[Alessandro Aste]

Hi,



Postresql version: 10.5



I need to convert an SQL field from real to numeric, but I’m getting a
strange behavior.



See the following query in preprod:



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



As you can see, the conversion to ::numeric is truncating the number to
just 1 decimal digit.



Also we tried to  change the schema definition of this table, from real to
numeric, and the value was truncated.



Is the ::varchar::numeric workaround a good option in your opinion? Any
other ideas to fix this issue?



Thank you,