On 10/18/18 3:28 AM, Alessandro Aste wrote:
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?
select 17637.75::real::numeric;
numeric
---------
17637.8
select 17637.75::float::numeric;
numeric
----------
17637.75
Thank you,
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx