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)
To round values of a whole column to n decimal places: SELECT ROUND(column_name::numeric, n) FROM table_name; Or using the CAST() function: For a specific value. To round a specific value: SELECT round(CAST(number_value AS NUMERIC), n);
How to Use ROUND() Function in PostgreSQL? To avail the functionalities of the ROUND() function, you have to follow the below syntax: ROUND(Number [ , n]); Here, in this syntax, the “Number” represents a numeric value to be rounded.
The TRUNC() function accepts two arguments. The precision argument is an integer that indicates the number of decimal places. If the precision argument is a positive integer, the TRUNC() function truncates digits to the right of the decimal point.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With