PostgreSQL ROUND Function

The ROUND() function in PostgreSQL rounds a numeric value to a specified number of decimal places. It's essential for formatting financial data, controlling precision in calculations, and presenting user-friendly numbers.

Syntax

-- Round to nearest integer
ROUND(numeric_value)

-- Round to specified decimal places
ROUND(numeric_value, decimal_places)

Official Documentation

Example usage

-- Round to nearest integer
SELECT ROUND(42.7) AS rounded;
-- Result: 43

SELECT ROUND(42.4) AS rounded;
-- Result: 42

-- Round to specific decimal places
SELECT ROUND(3.14159, 2) AS pi_rounded;
-- Result: 3.14

SELECT ROUND(1234.5678, 1) AS rounded;
-- Result: 1234.6

-- Round to tens, hundreds (negative decimal places)
SELECT ROUND(1234.56, -1) AS round_tens;
-- Result: 1230

SELECT ROUND(1234.56, -2) AS round_hundreds;
-- Result: 1200

-- Round prices for display
SELECT
    product_name,
    price,
    ROUND(price * 1.2, 2) AS price_with_tax
FROM products;

-- Round percentages
SELECT
    category,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM products
GROUP BY category;

-- Round averages
SELECT
    department,
    ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department;

-- Financial calculations
SELECT
    order_id,
    subtotal,
    ROUND(subtotal * 0.08, 2) AS tax,
    ROUND(subtotal * 1.08, 2) AS total
FROM orders;

-- Round currency conversion
SELECT
    amount_usd,
    ROUND(amount_usd * 1.35, 2) AS amount_cad,
    ROUND(amount_usd * 0.92, 2) AS amount_eur
FROM transactions;

-- Statistical rounding
SELECT
    test_name,
    ROUND(AVG(score), 1) AS avg_score,
    ROUND(STDDEV(score), 2) AS std_dev
FROM test_results
GROUP BY test_name;

Common issues

1. Banker's rounding: PostgreSQL uses "round half to even" (banker's rounding) by default in some contexts.
2. Data type: ROUND returns the same numeric type as input. May need explicit casting.
3. Negative decimals: Negative decimal places round to left of decimal point.
4. Precision vs scale: Be aware of NUMERIC type precision when storing rounded values.

Best practices

1. Always round financial calculations to 2 decimal places for currency.
2. Cast to NUMERIC before rounding for consistent behavior: ROUND(value::NUMERIC, 2).
3. Round at presentation layer when possible to preserve precision in calculations.
4. For always-round-up behavior, use CEILING; for always-round-down, use FLOOR.

Frequently Asked Questions

Q: How does PostgreSQL handle .5 values (e.g., 2.5)?
A: PostgreSQL uses "round half to even" (banker's rounding) for NUMERIC types: 2.5 rounds to 2, 3.5 rounds to 4. For floating-point types (REAL, DOUBLE PRECISION), it rounds half up: 2.5 rounds to 3.

Q: Can I round to tens, hundreds, or thousands?
A: Yes, use negative decimal places: ROUND(1234, -1) = 1230 (tens), ROUND(1234, -2) = 1200 (hundreds), ROUND(1234, -3) = 1000 (thousands).

Q: What's the difference between ROUND, FLOOR, and CEILING?
A: ROUND rounds to nearest value. FLOOR always rounds down: FLOOR(3.8) = 3. CEILING always rounds up: CEILING(3.2) = 4.

Q: How do I always round up (like for pricing)?
A: Use CEILING function: CEILING(19.01) = 20. Or for specific decimals: CEILING(price * 100) / 100 rounds up to nearest cent.

Q: Does ROUND work with INTEGER types?
A: Yes, but it returns the same value since integers are already whole numbers. ROUND is most useful with NUMERIC, DECIMAL, REAL, and DOUBLE PRECISION types.