Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL says function round(double precision, integer) does not exist

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

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

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1pg_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. 2You keep casting the same column to numeric in many queriesThe column is a float type but holds money or other exact valuesFix

What does the error look like?

  • ERROR: function round(double precision, integer) does not exist
  • function 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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.