PostgreSQL: Round Decimal

If you’ve ever worked with financial data, pricing, or any calculation involving decimals, you know the pain: floating-point arithmetic can give you results like 19.567000000000001 when you really just want 19.57. In production systems, you can’t display messy decimal chains to customers, and you definitely can’t store inexact values in financial records.

PostgreSQL’s ROUND() function is your answer. It handles decimal precision cleanly—whether you need 2 decimal places for currency, whole numbers for reporting, or rounding to larger units like the nearest hundred.

Quick Answer: Use ROUND() Function

To round decimal numbers to a specific number of decimal places:

SELECT
    price,
    ROUND(price, 2) as two_decimals,
    ROUND(price, 0) as whole_number,
    ROUND(price, -1) as nearest_ten
FROM products
WHERE price IS NOT NULL
LIMIT 5;

Returns:

price   | two_decimals | whole_number | nearest_ten
--------|--------------|--------------|-------------
19.567  | 19.57        | 20           | 20
123.456 | 123.46       | 123          | 120
89.45   | 89.45        | 89           | 90

The pattern here is simple: positive numbers round decimal places, negative numbers round to larger units (tens, hundreds, thousands).

How It Works

The ROUND() function uses standard rounding rules: if the digit after your target position is 5 or greater, round up; otherwise, round down. The second parameter determines where the rounding happens.

Here’s the key: the second parameter works in opposite directions from what some people expect:

  • Positive numbers (2, 3, 4): Round to that many decimal places. ROUND(19.567, 2) = 19.57
  • Zero (0): Round to the nearest whole number. ROUND(19.567, 0) = 20
  • Negative numbers (-1, -2, -3): Round to that many places before the decimal. ROUND(1234.5, -2) = 1200

This makes it incredibly flexible for different reporting needs. Accountants want 2 decimals. Dashboard displays might want whole numbers. Executives looking at big numbers want thousands or millions.

Different Approaches & When to Use Them

Approach 1: Standard Currency Rounding (2 Decimals)

When you’re working with financial data—prices, payments, balances—you almost always need exactly 2 decimal places:

SELECT
    invoice_id,
    subtotal,
    tax,
    ROUND(subtotal * 0.10, 2) as calculated_tax,
    ROUND(subtotal + (subtotal * 0.10), 2) as total_with_tax
FROM invoices
WHERE created_at > NOW() - INTERVAL '30 days';

This is the most common rounding pattern. You calculate values that might have floating-point artifacts, and ROUND(amount, 2) guarantees clean, display-ready numbers.

Approach 2: Round to Whole Numbers

Sometimes you don’t need decimals at all—maybe you’re reporting unit counts, summary statistics, or approximate values:

SELECT
    category,
    ROUND(AVG(price)) as average_price,
    ROUND(SUM(quantity)) as total_units
FROM products
GROUP BY category;

When you omit the second parameter or use ROUND(price, 0), you get whole numbers. This is particularly useful in executive reports where showing 0.47 units doesn’t make sense.

Approach 3: Round to Larger Units (Thousands, Millions)

For really large numbers, you often want to round to the nearest thousand or million for readability:

SELECT
    company_name,
    annual_revenue,
    ROUND(annual_revenue, -3) as nearest_thousand,
    ROUND(annual_revenue, -6) as nearest_million
FROM companies
WHERE annual_revenue > 1000000;

This gives you 1234567 rounded to 1235000 (nearest thousand) or 1000000 (nearest million). Great for annual reports and high-level dashboards.

Approach 4: ROUND vs TRUNC—Know When to Use Each

Here’s where people get confused: ROUND() uses rounding rules (5 and up rounds up), while TRUNC() just cuts off decimals. They give different results:

SELECT
    price,
    ROUND(price, 2) as rounded,
    TRUNC(price, 2) as truncated
FROM (
    SELECT 19.567 as price
    UNION ALL
    SELECT 19.564 as price
) data;

Results:

price  | rounded | truncated
-------|---------|----------
19.567 | 19.57   | 19.56
19.564 | 19.56   | 19.56

Use ROUND() when you want proper rounding (financial calculations, statistics). Use TRUNC() when you want to discard decimals without rounding (floor pricing, conservative estimates).

Real-World Example: E-Commerce Order Summary

Let me show you a practical scenario. You’re building an order summary page, and you need to calculate and display prices with proper rounding:

SELECT
    order_id,
    customer_name,
    product_count,
    ROUND(subtotal, 2) as subtotal,
    ROUND(subtotal * tax_rate, 2) as tax_amount,
    ROUND(shipping_cost, 2) as shipping,
    ROUND(subtotal + (subtotal * tax_rate) + shipping_cost, 2) as total_due,
    -- Also calculate per-unit for reporting
    ROUND(subtotal / product_count, 2) as avg_item_price
FROM orders
WHERE status IN ('pending', 'processing')
ORDER BY order_id DESC
LIMIT 50;

Here you’re rounding at every step: subtotal, tax, shipping, and the grand total. You’re also calculating average price per item rounded to 2 decimals. This ensures every number is display-ready and mathematically sound—no floating-point artifacts.

FAQ

Q: What’s the difference between ROUND and TRUNC? A: ROUND() uses standard rounding rules (5+ rounds up, below 5 rounds down). TRUNC() just cuts off decimals without rounding. ROUND(19.567, 2) = 19.57, but TRUNC(19.567, 2) = 19.56.

Q: How do I always round up, even if the digit is below 5? A: Use the CEIL() function with division: CEIL(price * 100) / 100 always rounds up to 2 decimals. This is useful for pricing where you never want to lose money on rounding.

Q: How do I always round down, even if the digit is 5 or higher? A: Use the FLOOR() function: FLOOR(price * 100) / 100. This always rounds down, useful for conservative estimates.

Q: What if I want banker’s rounding (round to nearest even)? A: PostgreSQL’s default ROUND() uses standard mathematical rounding. For banker’s rounding, you’d need custom logic, but this is rarely needed in PostgreSQL applications.

Q: Can I use ROUND in WHERE clauses? A: Yes, though it’s less efficient than filtering on exact values. WHERE ROUND(price, 2) > 10.00 works but won’t use indexes efficiently. It’s better to filter before rounding.

Q: What about rounding negative numbers? A: ROUND() handles negative numbers correctly: ROUND(-19.567, 2) = -19.57. Standard rounding rules apply regardless of sign.

Q: Can I round to different decimal places for different rows? A: Yes! The second parameter can be a column: ROUND(amount, decimal_places) where decimal_places is a column that varies by row.

Q: How does ROUND handle halfway values (exactly .5)? A: PostgreSQL uses “round half away from zero” behavior. ROUND(2.5) = 3, ROUND(-2.5) = -3. This is the standard rounding taught in schools.

Q: Performance impact? A: ROUND() is extremely fast—it’s a native function. You can use it on millions of rows without performance issues. It doesn’t require indexes or special optimization.

Q: How do I display leading zeros with ROUND? A: ROUND() just handles the number. To format with leading zeros, use TO_CHAR() after rounding: TO_CHAR(ROUND(value, 2), '000.00').

Common Errors & Solutions

Error: “function round(numeric, integer) doesn’t exist” This usually means the column isn’t numeric. Make sure you’re rounding a number, not a string. If it’s a string, cast it first: ROUND(amount::NUMERIC, 2).

Error: “Results differ from expected—negative numbers behaving oddly” Remember: negative parameter means rounding before the decimal. ROUND(-1234.567, -2) = -1200 (rounded to nearest hundred). This works the same for positive and negative numbers.

Error: “Rounding gives unexpected results” Check for floating-point arithmetic artifacts. If you’re multiplying or dividing and then rounding, sometimes floating-point precision causes surprises. Cast to NUMERIC for exact arithmetic: ROUND((19.99::NUMERIC * 1.1), 2).

Performance Tips

  • Very fast operation: ROUND() is a native function with virtually no overhead. Don’t worry about optimization—use it freely.
  • In WHERE clauses: While ROUND() works in WHERE clauses, filtering on the rounded value won’t use indexes. Filter on the original column when possible.
  • Combined with other functions: You can safely nest ROUND() with other functions: ROUND(ABS(value), 2) or ROUND(CAST(text_value AS NUMERIC), 2).
  • With aggregates: ROUND(SUM(amount), 2) or ROUND(AVG(price), 2) works efficiently—do the rounding after aggregation, not before.

Wrapping Up

The ROUND() function is essential for any database work involving decimals. Remember the pattern: positive numbers for decimals places, negative numbers for larger units. Use it confidently knowing it’s fast, reliable, and produces the results you expect. And when you need to discard decimals without rounding, that’s when TRUNC() comes into play.