Hannah Brooks’ Ohio invoice is $1,249.99 with 5.75% sales tax, so the raw tax is 71.874425. PostgreSQL ROUND trims that to cents: round(subtotal * tax_rate, 2) returns 71.87.
The catch: round(v, s) only accepts numeric. A double precision value gets “function round(double precision, integer) does not exist”.
The five examples go from cents to hundreds, then that error, averages and half-way values. Tested on PostgreSQL 18.6 for Windows.
PostgreSQL ROUND Function Syntax
Here is the syntax:
round ( numeric ) → numeric
round ( double precision ) → double precision
round ( v numeric, s integer ) → numeric
| Argument | Required | What it means |
|---|---|---|
v | Yes | Value to round. NULL gives NULL. |
s | No | Decimal places (default 0); negative rounds left of the point. NULL gives NULL. |
Return value: numeric for round(v, s); round(v) returns the input type.
Availability: all supported releases; the 16.4+ minor releases (August 2024) stopped clamping s at ±2000.
When to Use the ROUND Function
Use ROUND when you need to:
- show invoice tax to the cent
- report averages without 16 trailing digits
- give ballpark figures
- clean up float math before storing money
Not sure which type a column is? See PostgreSQL data types.
Run this once; every example uses it:
DROP TABLE IF EXISTS invoices;
CREATE TABLE invoices (
invoice_id int PRIMARY KEY,
customer varchar(20),
state char(2),
subtotal numeric(10,2),
tax_rate numeric(5,4),
hours double precision
);
INSERT INTO invoices VALUES
(1001, 'Hannah Brooks', 'OH', 1249.99, 0.0575, 7.25),
(1002, 'Tyler Reed', 'TX', 387.45, 0.0625, 3.5),
(1003, 'Grace Miller', 'CA', 2165.80, 0.0725, 12.333),
(1004, 'Marcus Bennett', 'NY', 64.10, 0.0400, 0.75),
(1005, 'Olivia Carter', 'FL', 918.00, 0.0600, NULL);
Example 1: Round a Number to 2 Decimal Places in PostgreSQL
Start with the common job: tax on the numeric subtotal and tax_rate columns.
You want it to the cent. Here is the query:
SELECT invoice_id,
subtotal * tax_rate AS raw_tax,
round(subtotal * tax_rate, 2) AS sales_tax
FROM invoices
WHERE invoice_id IN (1001, 1004);
Output:
invoice_id | raw_tax | sales_tax
------------+-----------+-----------
1001 | 71.874425 | 71.87
1004 | 2.564000 | 2.56
(2 rows)

For invoice 1001, this returns 71.87, and invoice 1004’s 2.564 becomes 2.56.
The product carries six decimals; s = 2 keeps two. I have a separate guide on totaling columns with PostgreSQL SUM.
Example 2: Round to the Nearest 10, 100 or 1,000
A negative s rounds left of the decimal point. The goal is a budget estimate:
SELECT invoice_id,
round(subtotal, -1) AS nearest_10,
round(subtotal, -2) AS nearest_100,
round(subtotal, -3) AS nearest_1000
FROM invoices
WHERE invoice_id IN (1001, 1004);
Output:
invoice_id | nearest_10 | nearest_100 | nearest_1000
------------+------------+-------------+--------------
1001 | 1250 | 1200 | 1000
1004 | 60 | 100 | 0
(2 rows)
For invoice 1001 ($1,249.99), this gives 1250, 1200 and 1000. Invoice 1004’s $64.10 rounds to 0 at -3.
Example 3: Fix “function round(double precision, integer) does not exist”
Now the error from the intro. hours is double precision, so hours * 85.50 is too.
Say you need labor at $85.50 an hour. Here’s the first try:
SELECT round(hours * 85.50, 2) FROM invoices;
Output:
psql:round-double-precision-error.sql:1: ERROR: function round(double precision, integer) does not exist
LINE 1: SELECT round(hours * 85.50, 2) FROM invoices;
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

The fix casts the product to numeric:
SELECT invoice_id, round((hours * 85.50)::numeric, 2) AS labor
FROM invoices
WHERE invoice_id IN (1001, 1005);
Output:
invoice_id | labor
------------+--------
1001 | 619.88
1005 |
(2 rows)
The fix returns 619.88 for invoice 1001. Olivia Carter’s NULL hours stay empty.
No round(double precision, integer) exists. My PostgreSQL CAST guide covers :: vs CAST.
Example 4: Round an AVG() Result to 2 Decimal Places
AVG on numeric returns 16 decimals. You want a clean average invoice and hours:
SELECT avg(subtotal) AS raw_avg,
round(avg(subtotal), 2) AS avg_invoice,
round(avg(hours)::numeric, 1) AS avg_hours
FROM invoices;
Output:
raw_avg | avg_invoice | avg_hours
----------------------+-------------+-----------
957.0680000000000000 | 957.07 | 6.0
(1 row)

That returns 957.07 dollars and 6.0 hours. Without ROUND, you should see 957.0680000000000000.
avg(hours) is double precision, so it needs ::numeric. AVG skipped the NULL row, as my PostgreSQL AVG guide explains.
Example 5: Does ROUND Round 2.5 Up or to Even?
It depends on the type. The goal is 2.5, 3.5 and -2.5 both ways, next to TRUNC and CEIL:
SELECT v,
round(v) AS round_numeric,
round(v::float8) AS round_double,
trunc(v), ceil(v)
FROM (VALUES (2.5), (3.5), (-2.5)) AS t(v);
Output:
v | round_numeric | round_double | trunc | ceil
------+---------------+--------------+-------+------
2.5 | 3 | 2 | 2 | 3
3.5 | 4 | 4 | 3 | 4
-2.5 | -3 | -2 | -2 | -2
(3 rows)

For 2.5, numeric returns 3 and double precision returns 2. -2.5 gives -3 and -2.
Per the docs, numeric ties go away from zero; double precision ties are platform dependent, usually to even. date_trunc is TRUNC for timestamps.
Tips & Common Mistakes
- Cast float math to numeric, or you hit
ERROR: function round(double precision, integer) does not exist. - NULL in, NULL out:
round(NULL::numeric, 2)andround(1.5, NULL)both return NULL. - A numeric(10,2) column rounds on insert: 19.995 became 20.00 and 19.994 became 19.99.
- For display,
to_char(1249.994, 'FM$9,999.00')returns $1,249.99 as text; to_number reverses it.
Wrapping Up
round(value, 2) on numeric is the call you’ll use most; copy Example 3 for float input, Example 4 for averages.
Next step: round per-state totals with PostgreSQL GROUP BY. The complete list of PostgreSQL functions has the math family, and the PostgreSQL 18 math functions page lists every signature.
More PostgreSQL number reading:
Frequently Asked Questions
How do I round up to 2 decimal places in PostgreSQL?
Use ceil(x * 100) / 100. For 24.211 it returned 24.2200000000000000; cast to numeric(10,2) for 24.22.
How is PostgreSQL ROUND different from SQL Server ROUND?
SQL Server’s ROUND has an optional third argument that truncates instead. PostgreSQL uses trunc(v, s).
Can ROUND keep trailing zeros like 5.10?
Yes, on numeric: round(5.1, 2) returns 5.10. A double precision 5.10 prints as 5.1.

Kumar Saurabh is a freelance software engineer who builds web and mobile apps with Next.js, React, Flutter and Python. From 2021 to 2024, Saurabh worked as an IT analyst and developer at TSInfo Technologies, writing technical articles on SQL and PostgreSQL. Saurabh writes the PostgreSQL, MySQL, MariaDB and MongoDB tutorials on DatabaseFAQs.com and holds a Master of Computer Applications (MCA) from Chandigarh University.