Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL ROUND

The `ROUND` function in PostgreSQL is a mathematical function used to round a numeric value to a specified number of decimal places. This function is essential for data manipulation when precision needs to be controlled in calculations or data presentation. It is part of PostgreSQL's suite of mathematical functions.

Usage

The `ROUND` function is employed when you need to round a number to a specific decimal precision, which is particularly useful in financial calculations and statistical data analysis.

ROUND(numeric_expression [, decimal_places])

In this syntax, `numeric_expression` is the number to be rounded, and `decimal_places` is optional, specifying how many decimal places to round to. If omitted, `ROUND` defaults to zero, rounding to the nearest integer. Note that `ROUND` follows the "round half up" rule, where numbers exactly halfway between rounded decimal values are rounded up.

Rounding with Negative Decimal Places

The `decimal_places` can also be negative, which means rounding will occur to the left of the decimal point.

Examples

1. Basic Rounding

SELECT ROUND(123.456);

This example rounds the number `123.456` to the nearest integer, resulting in `123`.

2. Rounding to Specific Decimal Places

SELECT ROUND(123.456, 2);

Here, the number `123.456` is rounded to two decimal places, yielding `123.46`.

3. Rounding Negative Numbers

SELECT ROUND(-123.456, 1);

This example demonstrates rounding the negative number `-123.456` to one decimal place, resulting in `-123.5`.

4. Rounding with Negative Decimal Places

SELECT ROUND(12345.678, -2);

In this case, the number `12345.678` is rounded to the nearest hundred, giving `12300`.

Tips and Best Practices

  • Specify decimal places as needed. Use the second argument to define precision explicitly when necessary.
  • Be cautious with financial data. Ensure rounding aligns with financial rules and standards to avoid discrepancies.
  • Combine with other functions. Use `ROUND` with aggregate functions like `AVG` to manage precision in result sets.
  • Consider performance. Rounding large datasets can impact performance; use it judiciously in queries.

Additional Information

  • Data Types: The `ROUND` function can be applied to `NUMERIC` and `DOUBLE PRECISION` data types, ensuring flexibility in its application.
  • Comparison with Other Functions: Unlike `ROUND`, the `CEIL` function always rounds up, `FLOOR` always rounds down, and `TRUNC` removes the fractional part without rounding. Understanding these differences can help you choose the right function for your needs.