Use of Round Function in Postgres SQL
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.
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:
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.
, ROUND(i_rating, 1) AS i_rating_round
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 irating 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:
Notice in the row where the tname_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”:
, ROUND(i_rating, 2) AS i_rating_round
Which gives us back the following results:
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.
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)).
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.”
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.
If no precision is supplied, zero is assumed.
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.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started