PostgreSQL offers several built-in math functions such as ROUND(), CEIL(), and FLOOR() to round a number up to specific decimal places. All these functions accept a numeric value as an argument and retrieve a rounded number.

This post will discuss the below-listed functions and their working through practical examples:

• What is ROUND() Function and How Does It Work in PostgreSQL?
• What is FLOOR() Function and How Does It Work in PostgreSQL?
• What is CEIL() Function and How Does It Work in PostgreSQL?
• How to Round Table Data in PostgreSQL?

So, let’s get started!

What is ROUND() Function and How Does It Work in PostgreSQL?

ROUND() is an inbuilt mathematical function in Postgres that can accept either one or two numbers as arguments.

ROUND(argument_1, argument_2);

The above snippet depicts that the ROUND() function accepts two arguments:

• The first one represents a number to be rounded, which is mandatory.
• If we pass only one argument, then the ROUND() function will skip the fractional points and retrieve the rounded numeric value.
• While the second argument is optional, it determines the number of fractional points.
• The ROUND() function retrieves the nearest/closest numeric value.

Example #1: Passing One Argument

Let’s pass only one number as an argument to the ROUND() function and see how the ROUND() function works in that case:

SELECT ROUND(572.172);

The output shows that the ROUND() function successfully rounded the specified number.

Example #2: Passing two Argument

In this example, we will pass two arguments to the ROUND() function. Consequently, you will notice that the first number will be rounded up to specific decimal places based on the second number:

SELECT ROUND(572.17214 , 2);

This way, you can round a number up to specific decimal places.

What is FLOOR() Function and How Does It Work in PostgreSQL?

Postgres offers another built-in math function named FLOOR() that rounds down the provided number to the next whole number (e.g12. 78 will be rounded down to 12). Use the following syntax for the FLOOR() function:

FLOOR(argument);

Example: How to Use FLOOR() Function in Postgres?

This example will show you how to round a positive or negative value using the FLOOR() function:

SELECT FLOOR(572.17214);

The FLOOR() function rounded down the given value.

Let’s pass a negative value to the FLOOR() function:

SELECT FLOOR(-572.77214);

Output proves that the FLOOR() function retrieves the rounded-down value.

What is CEIL() Function and How Does It Work in PostgreSQL?

Postgres offers another built-in math function named CEIL() that rounds up the provided number to the next whole number (e.g12. 78 will be rounded up to 13). Use the following syntax for the CEIL() function:

CEIL(argument);

Example: How to Use CEIL() Function in Postgres?

This example will show you how to round a positive or negative value using the CEIL() function:

SELECT CEIL(572.17214);

Output proves that the CEIL() function retrieves the rounded-up value.

Let’s pass a negative value to the CEIL() function:

SELECT CEIL(-572.77214);

The CEIL() function rounded up the given value.

How to Round Table Data in PostgreSQL?

We created a table named round_example that has only one column: original_val. The original _val column has some positive as well as negative values. Let’s utilize the ROUND(), CEIL(), and FLOOR() function on the orignal_val column and see how each function work on the table’s data:

SELECT original_val, 
ROUND(original_val), 
CEIL(original_val), 
FLOOR(original_val) 
FROM round_example;

The output will assist the users in analyzing the working of the ROUND(), CEIL(), and FLOOR() functions.

Conclusion

PostgreSQL offers several built-in math functions such as ROUND(), CEIL(), and FLOOR() to round a number up to specific decimal places. All these functions accept a numeric value as an argument and retrieve a rounded number. The ROUND() function retrieves the nearest/closest numeric value, the FLOOR() function rounds down the provided number to the next whole number, and the CEIL() function rounds up the given number to the next whole number. This post explained each method through practical examples.