Fix "function round(double precision, integer) does not exist" in Postgres
ERROR: function round(double precision, integer) does not exist
ROUND(value, 2) exists in Postgres, but only for the numeric type. Your value is a double precision (a float), so Postgres can't find a matching version of ROUND. Convert the value to numeric and it works.
Why it happens
- Casting to ::float to avoid integer division, then rounding the result.
- Rounding a function that returns double precision, like PERCENTILE_CONT() or date_part().
- A column defined as real or double precision.
How to fix it
1. Cast to numeric before rounding
Fails
SELECT department, ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary), 0) AS median_salary FROM employees GROUP BY department;
Works
SELECT department, ROUND((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary))::numeric, 0) AS median_salary FROM employees GROUP BY department;
PERCENTILE_CONT always returns double precision. Wrap it in parentheses and add ::numeric. ROUND(x) with no decimals argument does accept floats.
2. Use 100.0 instead of ::float
Fails
SELECT customer_id, ROUND(COUNT(*) FILTER (WHERE status = 'refunded')::float / COUNT(*) * 100, 1) AS refund_pct FROM orders GROUP BY customer_id;
Works
SELECT customer_id, ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'refunded') / COUNT(*), 1) AS refund_pct FROM orders GROUP BY customer_id ORDER BY customer_id;
100.0 is numeric, so the whole calculation is numeric and ROUND works. It also avoids integer division, which is why people reach for ::float in the first place.
3. Use EXTRACT instead of date_part
Fails
SELECT user_id,
ROUND(date_part('epoch', MAX(viewed_at) - MIN(viewed_at)) / 3600, 1) AS hours_active
FROM pageviews
GROUP BY user_id;Works
SELECT user_id, ROUND(EXTRACT(EPOCH FROM MAX(viewed_at) - MIN(viewed_at)) / 3600, 1) AS hours_active FROM pageviews GROUP BY user_id ORDER BY user_id;
date_part() returns double precision, while EXTRACT returns numeric in Postgres 14 and newer. On older versions, add ::numeric.
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.