Use of Round Function in Postgres SQL

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_techt_name_techt_category_techi_rating
0JavaLanguage4.335
1MongoNoSQL15.21
2MySQLDatabase5.16
3PostgreSQLDatabase17.3
4PythonLanguage14.382
5PHPLanguage12.6218
6JavascriptLanguage12

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.

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 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:

t_name_techi_rating_round
Java4.3
Mongo15.2
MySQL5.2
PostgreSQL17.3
Python14.4
PHP12.6
Javascript12.0

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:

id_techt_name_techt_category_techi_rating
0JavaLanguage4.335
1MongoNoSQL15.21
2MySQLDatabase5.16
3PostgreSQLDatabase17.3
4PythonLanguage14.382
5PHPLanguage12.6218
6JavascriptLanguage12

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

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

Which gives us back the following results:

t_name_techi_rating_round
Java4.34
Mongo15.21
MySQL5.16
PostgreSQL17.30
Python14.38
PHP12.62
Javascript12.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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.