'ERROR: function round(double precision, integer) does not exist

I am in the middle of migration some queries which have been running for ages with MySQL database which is now in Postgres having the same structure. I got stuck with simple round function which ends with the following error message.

ERROR: function round(double precision, integer) does not exist

part of the select which does not work: 

round(floor(pools.available_capacity_in_kb/1024/1024/1024*100)/100,2) as free,

pools.available_capacity_in_kb is stored as BIGINT in the database (Postgres 10.9)

sqlpostgresqlroundingpostgres-10


Solution 1:[1]

I had the same problem with geograpic coordinates. The longitude was in double precision from the open street map data and needed a rouded value.

My solution work fine:

select ROUND(CAST(longitude AS numeric),2) from my_points;

Solution 2:[2]

Besides the type CAST syntax, you can use the following syntax to convert a value of one type into another (cast :: operator):

select ROUND(value::numeric, 2) from table_x;

Notice that the cast syntax with the cast operator (::) is PostgreSQL-specific and does not conform to the SQL standard.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gerd
Solution 2 Alejandro Veltri

Related Questions