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)

Sulfamerazine

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.

Centonze

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. – Misapprehend

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/1024/1024*100)/100,2) as free, – Sulfamerazine

@radeklopatecki so please, close this task – Centonze

Is there a SQL equivalent to \df? – Groceryman

@Groceryman - select oid::regprocedure from pg_proc where proname like '%round%'; – Centonze

Answer 2

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; 

Anguilliform

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

Answer 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.

Sosanna

I was having an expression inside the round function as first parameter, that expression was returning 'double precision' value. After so much efforts I finally could solve the issue by enclosing the expression in bracket and type casting the result to numeric. Thanks to this answer. – Hoberthobey

Answer 4

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.

Vindicable

Answer 5

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

Bosnia