sqlpostgresqlroundingpostgres-10

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

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)

Solution

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