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

sql postgresql rounding postgres-10
radek lopatecki picture radek lopatecki · Nov 6, 2019 · Viewed 28.5k times · Source

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)

Answer

Gerd picture Gerd · Mar 25, 2021

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;

Related questions

Discard millisecond part from timestamp

How can I discard/round the millisecond part, better if the second part is also removed from a timestamp w/o timezone ?

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More