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

Question

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)

Solution 1

The core of the problem is somewhere else. PostgreSQL uses long division for integer and bigint numbers (when on both parts of division are int, bigint values). So result of pools.available_capacity_in_kb/1024/1024/1024*100)/100 is bigint. Probably this is not, what you expect.

postgres=# \df round
                          List of functions
+------------+-------+------------------+---------------------+------+
|   Schema   | Name  | Result data type | Argument data types | Type |
+------------+-------+------------------+---------------------+------+
| pg_catalog | round | double precision | double precision    | func |
| pg_catalog | round | numeric          | numeric             | func |
| pg_catalog | round | numeric          | numeric, integer    | func |
+------------+-------+------------------+---------------------+------+
(3 rows)

There is not any round function for bigint (because it has not any sense). Please try to fix it by using float division like

pools.available_capacity_in_kb/1024/1024/1024*100)/100.0

Now, the result will be numeric, and the function round(numeric, int) exists - so it should works.

Solution 2

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 3

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.

Comments

[Belayer]

The function sequence "round(floor( ..." is not necessary, and does not make since anyway. The floor function returns "the largest integer less that or equal to the argument", You cannot round an integer - at least not without an UDF.

[radek lopatecki]

Thanks a lot for a quick turnaround Pavel. You are right - round function is not working on bigint. I changed data type to numeric and that worked well. round(floor(pools.available_capacity_in_kb::numeric/1024/102‌​4/1024*100)/100,2) as free,

[Pavel Stehule]

@radeklopatecki so please, close this task

[thedatadavis]

The solution worked for me when I ran into the issue while using Postgres 9.6 in db-fiddle