Ошибка функция round double precision integer не существует - Не ошибается лишь тот, кто ничего не делает!

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)

funnydman

8,6034 gold badges36 silver badges52 bronze badges

asked Nov 6, 2019 at 14:03

radek lopateckiradek lopatecki

6031 gold badge5 silver badges5 bronze badges

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;

answered Mar 25, 2021 at 13:11

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.

answered Aug 8, 2021 at 11:15

funnydmanfunnydman

8,6034 gold badges36 silver badges52 bronze badges

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.

Dave Yu

3064 silver badges15 bronze badges

answered Nov 6, 2019 at 14:18

Pavel StehulePavel Stehule

41.8k5 gold badges90 silver badges93 bronze badges

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.

answered Sep 24, 2022 at 16:31

j kj k

311 silver badge6 bronze badges

Источник

написал запрос

select
              'request_median_duration' as metric_name,
              EXTRACT(EPOCH FROM request_median_duration)/60 as metric_value,
              TO_CHAR(NOW(), 'YYYY-MM-DD HH:MM:SS') as push_dttm
              from mp_statistics

нужно округлить до 2 знака до запятой
попробовал сделать так

select
              'request_median_duration' as metric_name,
              ROUND(EXTRACT(EPOCH FROM request_median_duration)/60,2) as metric_value,
              TO_CHAR(NOW(), 'YYYY-MM-DD HH:MM:SS') as push_dttm
              from mp_statistics

возвращается ошибка

SQL Error [42883]: ERROR: function round(double precision, integer) does not exist
  Подсказка: No function matches the given name and argument types. You might need to add explicit type casts.
  Позиция: 94

выкрутился так

select
              'request_median_duration' as metric_name,
              ROUND(EXTRACT(EPOCH FROM request_median_duration)/60*100)/100 as metric_value,
              TO_CHAR(NOW(), 'YYYY-MM-DD HH:MM:SS') as push_dttm
              from mp_statistics

какой есть еще более изящный вариант?

Источник

Попробуйте также старый синтаксис для кастинга,

SELECT ROUND(AVG(some_column)::numeric,2)    
FROM table;

работает с любой версией PostgreSQL.

В некоторых функциях PostgreSQL есть недостатки, почему (???): Я думаю, что «это недостаток» (!), но @CraigRinger, @Catcall и команда PostgreSQL согласитесь о «историческом обосновании».

PS: еще один вопрос о округлении — точность, проверьте @Ответ IanKenney.

Перегрузка как стратегия литья

Вы можете overload использовать функцию ROUND с помощью

 CREATE FUNCTION ROUND(float,int) RETURNS NUMERIC AS $$
    SELECT ROUND($1::numeric,$2);
 $$ language SQL IMMUTABLE;

Теперь ваша инструкция будет работать нормально, попробуйте (после создания функции)

 SELECT round(1/3.,4); -- 0.3333 numeric

но он возвращает тип NUMERIC… Чтобы сохранить первую перегрузку использования commom, мы можем вернуть FLOAT-тип, когда предлагается параметр TEXT,

 CREATE FUNCTION ROUND(float, text, int DEFAULT 0) 
 RETURNS FLOAT AS $$
    SELECT CASE WHEN $2='dec'
                THEN ROUND($1::numeric,$3)::float
                -- ... WHEN $2='hex' THEN ... WHEN $2='bin' THEN... complete!
                ELSE 'NaN'::float  -- like an error message 
            END;
 $$ language SQL IMMUTABLE;

Try

 SELECT round(1/3.,'dec',4);   -- 0.3333 float!
 SELECT round(2.8+1/3.,'dec',1); -- 3.1 float!
 SELECT round(2.8+1/3.,'dec'::text); -- need to cast string? pg bug

PS: проверка df round после перегрузок отобразит что-то вроде

 Schema     |  Name | Result data type | Argument data types 
------------+-------+------------------+----------------------------
 myschema   | round | double precision | double precision, text, int
 myschema   | round | numeric          | double precision, int
 pg_catalog | round | double precision | double precision            
 pg_catalog | round | numeric          | numeric   
 pg_catalog | round | numeric          | numeric, int

Функции pg_catalog являются стандартными, см. руководство по встроенным математическим функциям.

Источник

Solution 1

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

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)
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.
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,
@radeklopatecki so please, close this task
The solution worked for me when I ran into the issue while using Postgres 9.6 in db-fiddle

Recents

Источник

With current master, the postgresql tests fail with this error (among others):

====================================================================
PostGIS(1): Query: DECLARE qgis_1166 BINARY CURSOR FOR SELECT "key1"::text,"key2"::text,"pk"::text,"cnt"::text,"name"::text,"name2"::text,"num_char"::text FROM "qgis_test"."someDataCompound" WHERE (round(("cnt" / ((66.67)::real)),0) <= 2) returned 7 [ERROR:  function round(double precision, integer) does not exist                                        ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
]
PostGIS(1): 1 cursor states lost.
SQL: DECLARE qgis_1166 BINARY CURSOR FOR SELECT "key1"::text,"key2"::text,"pk"::text,"cnt"::text,"name"::text,"name2"::text,"num_char"::text FROM "qgis_test"."someDataCompound" WHERE (round(("cnt" / ((66.67)::real)),0) <= 2)
LINE 1: ...::text FROM "qgis_test"."someDataCompound" WHERE (round(("cn...
                                                    ==
FAIL: testStyleDatabaseWithService (__main__.TestPyQgsPostgresProvider)
Test saving style in DB using a service file.
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/src/qgis/qgis-master/tests/src/python/test_provider_postgres.py", line 1318, in testStyleDatabaseWithService
    self.assertEqual(len(ids), 1)
AssertionError: 0 != 1

I’m running it against PostgreSQL-9.6.9
Indeed round(float8, int) does not exist, while round(numeric, int) does:

strk=# select round(1.01::float8, 2);
ERROR:  function round(double precision, integer) does not exist
LINE 1: select round(1.01::float8, 2);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
strk=# select round(1.01::numeric, 2);
 round
-------
  1.01
(1 row)

strk=# select version();
                                             version                                              
--------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.9 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.2.0-8ubuntu3.2) 7.2.0, 64-bit
(1 row)

Источник

Перегрузка как стратегия литья

Solution 1

Solution 2

Solution 3

Comments

Recents

Related