PostgreSQL says function round(double precision, integer) does not exist
ERROR: function round(double precision, integer) does not existShort answer
PostgreSQL only has a two-argument round() for numeric. A double precision or real value has no round(x, n), so the call fails. Cast the value to numeric before rounding, for example round(price::numeric, 2), or store the column as numeric if you need exact decimals.
Fix it fast
1Find out which cause you have
Find the type of the value you pass to round(). If it says double precision or real, this page applies.
SELECT pg_typeof(price) FROM products LIMIT 1;2Match what you see, then jump to its fix
- 1
pg_typeof shows double precision, as with a float8 column, avg() of floats or a calculation with a decimal point and a floatA float value is passed to the two-argument round()Fix - 2
You keep casting the same column to numeric in many queriesThe column is a float type but holds money or other exact valuesFix
- 1
What does the error look like?
ERROR: function round(double precision, integer) does not existfunction round(double precision, integer) does not exist
Cause 1 of 2
A float value is passed to the two-argument round()
1Check
The function table lists round(numeric), round(double precision) and round(numeric, integer). There is no round(double precision, integer).
SELECT pg_typeof(price), pg_typeof(price::numeric) FROM products LIMIT 1;2Fix
Cast the value to numeric. Both cast forms are valid; ::numeric is shorter.
SELECT round(price::numeric, 2) FROM products;
SELECT round(CAST(price AS numeric), 2) FROM products;3Verify
The query returns values with two decimal places.
SELECT round(42.4382::double precision::numeric, 2);Confirmed by postgresql.org
Cause 2 of 2
The column is a float type but holds money or other exact values
1Check
The manual calls floating-point types inexact and recommends numeric for monetary amounts and other values that must be exact. It also notes that numeric rounds ties away from zero while double precision usually rounds ties to the nearest even number, so 2.5 rounds to 3 for numeric and 2 for double precision.
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'products';2Fix
Change the column to numeric with a fixed scale. The manual says this normally rewrites the whole table and its indexes, and can need up to double the disk space, so plan it for a quiet period on large tables, then run ANALYZE.
ALTER TABLE products
ALTER COLUMN price TYPE numeric(12,2)
USING price::numeric(12,2);3Verify
Run the original query without a cast.
SELECT round(price, 2) FROM products LIMIT 5;Confirmed by postgresql.org
How do I stop it coming back?
- Use numeric for prices, balances and any value people compare to the cent.
- Cast once in a view or a generated column rather than in every query.
- When an ORM sends parameters, check their SQL type with pg_typeof so a float does not slip in.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix