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

Question

I am in the middle of migrating some queries which have been running for ages with MySQL database which is now in Postgres having the same structure. I got stuck with a 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 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.

Answer 2

In my case, the column that I was trying to aggregate with AVG() was been returned as a Set (look at the error message: "ERROR: function avg(double precision[]) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts").

So, the solution in my case was to do a SubQuery and use the Unnest() function, this way:

Select avg(unnested_field) from
( 
      Select unnest(field) as unnested_field
      From my_table
) as Subquery

Answer 3

I had the same problem with geographic 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; 

Answer 4

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.

Answer 5

you can use following syntax as well

select round((value::Decimal),2) from table_name;

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