Postgres round() function

The Postgres round() function rounds numeric values to a specified number of decimal places or the nearest integer.

It can help maintain consistency in numerical data, simplify complex decimal numbers, and adjust the precision of calculations to meet specific requirements. It's particularly useful in financial calculations, data analysis, and for presenting numerical data in a more readable format.

Function signature

The round() function has a simple form:

round(number [, decimal_places]) -> number

• number: The input value to be rounded. It can be of any numeric data type — integer, floating-point, or decimal.
• decimal_places: An optional integer that specifies the number of decimal places to round to. If omitted, the input number is rounded to the nearest integer.

Example usage

Let's consider a table product_sales that tracks sales data for various products. We'll use the round() function to adjust the precision of our sales figures.

WITH product_sales(product_id, sales_amount) AS (
  VALUES
    (1, 1234.5678),
    (2, 2345.6789),
    (3, 3456.7890),
    (4, 4567.8901)
)
SELECT
  product_id,
  sales_amount,
  round(sales_amount) AS rounded_to_integer,
  round(sales_amount, 2) AS rounded_to_cents
FROM product_sales;

This query demonstrates using the round() function to round sales amounts to the nearest integer and to two decimal places (cents).

product_id | sales_amount | rounded_to_integer | rounded_to_cents
------------+--------------+--------------------+------------------
          1 |    1234.5678 |               1235 |          1234.57
          2 |    2345.6789 |               2346 |          2345.68
          3 |    3456.7890 |               3457 |          3456.79
          4 |    4567.8901 |               4568 |          4567.89
(4 rows)

Other examples

Using round() to calculate accurate percentages

The round() function is often used when calculating and displaying percentages. For example, consider a table with sales data for different products. Let's calculate the percentage of total sales contributed by each product.

WITH product_sales(product_id, sales_amount) AS (
  VALUES
    (1, 1234.56),
    (2, 2345.67),
    (3, 3456.78),
    (4, 4567.89)
)
SELECT
  product_id,
  sales_amount,
  round(
    (sales_amount / SUM(sales_amount) OVER ()) * 100,
    2
  ) AS percentage_of_total
FROM product_sales
ORDER BY percentage_of_total DESC;

This query calculates each product's contribution to total sales and rounds the percentage to two decimal places. This avoids displaying overly precise percentages that can be misleading.

product_id | sales_amount | percentage_of_total
------------+--------------+---------------------
          4 |      4567.89 |               39.36
          3 |      3456.78 |               29.79
          2 |      2345.67 |               20.21
          1 |      1234.56 |               10.64
(4 rows)

Combining round() with other functions

We can combine round() with other functions for more complex calculations. For example, let's calculate the average order value and round it to the nearest dollar and the nearest cents:

WITH orders(order_id, total_amount) AS (
  VALUES
    (1, 123.45),
    (2, 234.56),
    (3, 345.67),
    (4, 456.78),
    (5, 567.89)
)
SELECT
  round(AVG(total_amount)) AS avg_order_value_rounded,
  round(AVG(total_amount), 2) AS avg_order_value_cents
FROM orders;

avg_order_value_rounded | avg_order_value_cents
-------------------------+-----------------------
                     346 |                345.67

Additional considerations

Rounding behavior

Postgres round() function uses the half-round-up method for tie-breaking. This means that when the input is exactly halfway between two numbers, it rounds up to the higher number. For example:

SELECT round(2.65, 1), round(2.75, 1);

This query rounds both 2.65 and 2.75 to the next higher number with one decimal place:

round | round
-------+-------
   2.7 |   2.8
(1 row)

Financial calculations often require banker's rounding (also known as round-to-even) to minimize bias. If you need this behavior, you can implement it using a custom function or by combining round() with other functions.

Performance implications

The round() function is generally fast, but frequent use in large datasets might impact performance. If you need to round values frequently in queries, consider storing pre-rounded values in a separate column and creating a function index on it.

Alternative functions

• ceil() and floor(): These functions round up or down to the nearest integer, respectively.
• trunc(): This function truncates a number to a specified number of decimal places without rounding.

Resources

• PostgreSQL documentation: Mathematical Functions and Operators
• PostgreSQL documentation: Numeric Types