PostgreSQL ROUND Function

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
ArgumentRequiredWhat it means
vYesValue to round. NULL gives NULL.
sNoDecimal 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)
PostgreSQL round(subtotal * tax_rate, 2) returning 71.87 sales tax in VS Code
round(v, 2) keeps cents.

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.
PostgreSQL error function round(double precision, integer) does not exist with the HINT about explicit type casts
The error and its HINT.

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)
PostgreSQL round(avg(subtotal), 2) returning 957.07 next to the raw 957.0680000000000000
Round the AVG result.

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)
PostgreSQL round(2.5) returns 3 for numeric and 2 for double precision
numeric vs double precision ties.

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) and round(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.