/ ID: 795 Title: Use of Round Function in Postgres SQL 795 Meta Description: This article shows how to use the Round function in Postgres SQL. Code samples included. Meta Keywords: Round Function Postgres SQL Author: orkb Template: Unstructured Tutorial Categories: PostgreSQL Tags: PostgreSQL, Round, Function, SQL, Ceiling, Floor, Trunc Status: Published /

Introduction

In this article, we will study the Round function in Postgres SQL from three perspectives, including:

What? What does the ROUND() function do and what is the syntax?
Why? In what circumstances do we use the ROUND function in our Postgres SQL?
How? How do we use this function in our SQL database queries?
Similar functions We'll also look at some functions that behave similar to the way ROUND does, which may be more useful than the ROUND function for specific needs. These include CEILING(), FLOOR(), and TRUNC().

What is this ROUND function and how does it work?

The PostgreSQL ROUND function returns a number after rounding the initial number to a certain precision. By "precision" here, we mean a certain number of decimal places.

Syntax

Round(i_number_to round, [i_precision])

Note here that the precision parameter above is optional. If none is supplied, Postgres assumes zero.

Why use the ROUND function?

Now we'll play with the ROUND function in a database environment using SQL so as to best understand how we can incorporate use of the ROUND function into our Postgres-based projects. Let's begin with some sample data, a table called technologies:

| id_tech | t_name_tech | t_category_tech | i_rating | |--------:|-------------|-----------------|---------:| | 0 | Java | Language | 4.335 | | 1 | Mongo | NoSQL | 15.21 | | 2 | MySQL | Database | 5.16 | | 3 | PostgreSQL | Database | 17.3 | | 4 | Python | Language | 14.382 | | 5 | PHP | Language | 12.6218 | | 6 | Javascript | Language | 12 |

We'll now write some SQL that pulls a rating of each technology from the i_rating field/column of the technologies table above. The twist: we want to have that number we pull to be consistently in the format of ##.#, i.e., having precision to only one decimal.

SQL SELECT t_name_tech , ROUND(i_rating, 1) AS i_rating_round FROM technologies

Analysis of the SQL above:

SELECT: We chose two columns to return from our table; t_name_tech and i_rating_round.
ROUND: This tells PostgreSQL to take the number in the i_rating column, round it up to 1 decimal, and name the result AS i_rating_round.
FROM: For the above commands, use the table called "technologies" as the source of our query data.

Executing the SQL query above returns the following recordset of data:

| t_name_tech | i_rating_round | |-------------|---------------:| | Java | 4.3 | | Mongo | 15.2 | | MySQL | 5.2 | | PostgreSQL | 17.3 | | Python | 14.4 | | PHP | 12.6 | | Javascript | 12.0 |

Notice in the row where the t_name_tech column has a value of "Python", how the Round() function turned 14.382 into 14.4? That's because Round() looks at the number after our designated decimal cut-off position, 1 in this case, and asks, "Is this number equal to or greater than 5?" If the answer is yes, then the preceding number, at position 1, is incremented by one. So 14.38 turns into 14.4 because the 8 here is greater than 5.

What if we were to round, but with a precision of 2 instead of 1? First, let's start out with our original data:

Now we modify our query, specifically, what precision we are asking of the ROUND function, changing the "1" to a "2":

SQL SELECT t_name_tech , ROUND(i_rating, 2) AS i_rating_round FROM technologies

Which gives us back the following results:

| t_name_tech | i_rating_round | |-------------|---------------:| | Java | 4.34 | | Mongo | 15.21 | | MySQL | 5.16 | | PostgreSQL | 17.30 | | Python | 14.38 | | PHP | 12.62 | | Javascript | 12.00 |

Notice what happened here? When Postgres didn't have numbers to go on, because the original value's precision wasn't "deep" enough, the SQL engine used a zero value for that decimal position.

Similar functions

Before we wrap up about the ROUND function: there may be situations where you first thought the solution would be to use Round() but actually, the Ceiling(), Floor(), or Trunc() functions may work better. Let's take a brief look at each of those functions:

CEILING(): This function rounds the decimal up to the next higher integer. The decimal is eliminated when the number is rounded up to an integer. Put another way, "Take our number, round up, and make it an integer." If Postgres SQL had an INTEGER function, CEILING could be mimic'd like so: INTEGER(ROUND(x,1)).

Examples

CEILING(2.7) returns 3

CEILING(2.3) returns 2

FLOOR(): ThiS function rounds the decimal up to the next higher integer. The decimal is eliminated when the number is rounded up to an integer. We could say it like, "Round your number down and make it an integer."

Examples

FLOOR(2.7) returns 2

FLOOR(2.3) returns 2

TRUNC(): This function does not round, it cuts the original number at the specified precision point supplied to the function. If no precision parameter is supplied, the decimal is eliminated, thus creating a whole number. Put another way, "Take our number and dispense with everything after the decimal." And there's more.

Syntax

TRUNC(i_original_number, [i_precision])

If no precision is supplied, zero is assumed.

Examples

TRUNC(2.75) returns 2

TRUNC(2.31) returns 2

TRUNC(2.31, 1) returns 2.3

Note: It's important to distinguish between TRUNC() function and the TRUNCATE command in Postgres.

Conclusion

In this article we practiced the use of the ROUND() function in coding and explored why one may want to use the ROUND function in PostgreSQL.