PostgreSQL ROUND() Function

PostgreSQL ROUND() Function

The PostgreSQL ROUND() function is used to round a numeric value to the nearest integer or to a specified number of decimal places. This function is essential for formatting numerical data to the desired precision.

Syntax

ROUND(number, decimal_places)

The ROUND() function has the following components:

  • number: The numeric value to be rounded.
  • decimal_places: The number of decimal places to round to (optional).

Example PostgreSQL ROUND() Queries

Let's look at some examples of PostgreSQL ROUND() function queries:

1. Basic ROUND() Example

SELECT ROUND(4.567) AS rounded_value;

This query rounds 4.567 to the nearest integer, which is 5.

2. ROUND() with Specified Decimal Places

SELECT ROUND(4.567, 2) AS rounded_value;

This query rounds 4.567 to two decimal places, which is 4.57.

3. ROUND() with Column Values

SELECT value, ROUND(value, 1) AS rounded_value
FROM numbers;

This query retrieves the value and its rounded value to one decimal place from the numbers table.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the ROUND() function to format numerical values.

Step 1: Creating a Table

This step involves creating a new table named numbers to store numerical data.

CREATE TABLE numbers (
    id SERIAL PRIMARY KEY,
    value NUMERIC
);

In this example, we create a table named numbers with columns for id and value.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the numbers table.

INSERT INTO numbers (value)
VALUES (1.234),
       (2.345),
       (3.456),
       (4.567);

Here, we insert data into the numbers table.

Step 3: Using the ROUND() Function

This step involves using the ROUND() function to format the numerical values from the numbers table.

-- Basic ROUND()
SELECT value, ROUND(value) AS rounded_value
FROM numbers;

-- ROUND() with Specified Decimal Places
SELECT value, ROUND(value, 2) AS rounded_value
FROM numbers;

These queries demonstrate how to use the ROUND() function to format the numerical values from the numbers table, including basic usage and rounding to specified decimal places.

Conclusion

The PostgreSQL ROUND() function is a fundamental tool for formatting numerical data to the desired precision by rounding values to the nearest integer or to a specified number of decimal places. Understanding how to use the ROUND() function and its syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.