Function Round Double Precision Does Not Exist
Calling round (or another function) with two arguments on a floating-point value raises 'function round(double precision, integer) does not exist', with...
In 10 seconds
A dashboard query that rounds an average to two decimals -- round(avg(amount), 2) -- fails with 'ERROR: function round(double precision, integer) does not exist'. The amount column is double precision, so avg(amount) is double precision, and PostgreSQL ships a two-argument round only for numeric, not for double precision. This is one of the most common surprises in PostgreSQL: round(x) works on a float, but round(x, 2) does not, because the scale-taking form is numeric-only. The cure is a cast to numeric (and, longer term, storing amounts as numeric so the cast is never needed).
Why This Happens
PostgreSQL resolves a function call by its name and the types of its arguments. The two-argument round, which rounds to a given number of decimal places, is defined only for numeric: round(numeric, integer). There is a one-argument round(double precision) (round to the nearest integer), which is why round(value) succeeds, but there is deliberately no round(double precision, integer). avg over a double precision column yields double precision, so round(avg(amount), 2) asks for a signature that does not exist and raises 42883. Casting the value to numeric -- round(avg(amount)::numeric, 2) -- selects the round(numeric, integer) overload and also gives exact decimal rounding, which is what reports and money want.
The deeper reason this keeps happening is the column type: floating point is the wrong type for exact decimal amounts, so storing them as numeric avoids both the error and the rounding imprecision. The deeper reason this keeps happening is the column type: floating point is the wrong type for exact decimal amounts, so storing them as numeric avoids both the error and the rounding imprecision.
Diagnose
Free Tier -- Basic Approach
Show the type that broke the resolution. avg(amount) is double precision, and the two-argument round simply has no double precision form -- that single fact is the whole cause.
SELECT pg_typeof(avg(amount)) AS avg_type FROM sales;
avg_type ------------------ double precision (1 row)
SELECT pg_typeof(avg(amount)) FROM sales returns double precision, telling you exactly which type round(., 2) could not match.
Fix
Cast the value to numeric so the two-argument round resolves. round(avg(amount)::numeric, 2) selects round(numeric, integer) and returns an exact two-decimal average. This is the literal capture.
SELECT round(avg(amount)::numeric, 2) AS avg_amount FROM sales;
avg_amount
------------
166.83
(1 row)
SELECT round(avg(amount)::numeric, 2) AS avg_amount FROM sales; returns 166.83 -- the cast is on the value going into round, not on the result.
Verify
Confirm the healthy state: the cast query returns a rounded two-decimal value, and the bare two-argument round on double precision STILL errors -- proving the fix was the cast, and that the function genuinely does not exist for this type.
SELECT round(avg(amount)::numeric, 2) AS avg_amount FROM sales;
avg_amount
------------
166.83
(1 row)
SELECT round(avg(amount), 2) FROM sales;
ERROR: 42883: function round(double precision, integer) does not exist
LINE 1: SELECT round(avg(amount), 2) FROM sales;
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
LOCATION: ParseFuncOrColumn, parse_func.c:629
round(avg(amount)::numeric, 2) returns 166.83; round(avg(amount), 2) still raises 'function round(double precision, integer) does not exist', which is the correct, expected behaviour.
⏪ Rollback
All work runs in a throwaway database on a disposable test environment, so cleanup is just dropping that database; the persistent demo containers are never touched. The failing query changed nothing, so there is nothing to undo. The fix is a read-only change to the query (adding ::numeric), which carries no risk. The durable prevention -- changing a column from double precision to numeric -- is a schema change that does rewrite the table and should be done in a maintenance window with the application's rounding/formatting code reviewed, but it is not part of resolving the immediate error.
📦 Version Matrix (PG 14--18)
Run the same break-and-fix on PostgreSQL 14, 15, 16, 17, and 18: the two-argument round on double precision raises the identical 'round(double precision, integer) does not exist' on every version, and the ::numeric cast resolves it identically (cast_ok). The round overload set is the same across all supported releases.
| Version | Behavior | Version Notes |
|---|---|---|
PG 14.23 |
broken=round(double precision, integer) does not exist | ✗ Not available -- use stats_reset age |
PG 15.18 |
broken=round(double precision, integer) does not exist | ✗ Not available -- use stats_reset age |
PG 16.14 |
broken=round(double precision, integer) does not exist | ✗ Not available -- use stats_reset age |
PG 17.10 |
broken=round(double precision, integer) does not exist | ✗ Not available -- use stats_reset age |
PG 18.4 |
broken=round(double precision, integer) does not exist | ✗ Not available -- use stats_reset age |
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
Why does round(x) work but round(x, 2) does not on a float?
round(double precision) (one argument, round to nearest integer) exists, but the two-argument scale form round(value, decimals) is defined only for numeric. So on a double precision value the one-argument call resolves and the two-argument call raises 42883.
What is the smallest fix for the failing query?
Cast the value to numeric inside the call: round(avg(amount)::numeric, 2). That selects round(numeric, integer), returns an exact two-decimal result, and changes nothing else.
How do I confirm there really is no round(double precision, integer)?
List the overloads from the catalog: SELECT proname, pg_get_function_arguments(oid) FROM pg_proc WHERE proname = 'round'. You will see round(double precision), round(numeric), and round(numeric, integer) -- and nothing for (double precision, integer).
Should I just change the column to numeric?
For amounts that need exact decimals (money, reported figures), yes -- numeric avoids both the 42883 and floating-point rounding error, and lets round(avg(x), 2) work with no cast. It is a table-rewriting change, so plan it for a maintenance window; the cast is the immediate unblock in the meantime.