Round in CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this document, we will study Round in CockroachDB from more than one perspective, including:

  • What does the Cockroach ROUND function do and what is the syntax?
  • In what circumstances do we use the ROUND function in our Cockroach SQL scripts?
  • How do we use the ROUND function in our Cockroach database queries?
  • In addition, we’ll look at some functions that behave similar to the way ROUND does, some of which may be more useful than the ROUND function for specific needs. These include FLOOR, CEILING, and TRUNC.

What is Cockroach ROUND?

The CockroachDB ROUND function returns a number after rounding the initial number to a certain precision. By “precision” here, we mean a specific number of decimal places.

Syntax of Round

1
intRounded := Round(Number_to_round, [intPrecision])

Notice here that the intPrecision parameter you see above as the second parameter is optional. If this parameter is not supplied, Cockroach assumes zero.

Why use the ROUND function? We will make use of the ROUND function in a Cockroach database environment using SQL so as to best understand how we can incorporate use of the ROUND function into our Cockroach-based projects. Let us start with some sample data, a table called tblTechnologies:

idTechtxtTechnologytxtCatTechintRating
1JavaLanguage5.335
2MongoNoSQL16.21
3PostgresDatabase26.16
4CockroachDBDatabase18.3
5PythonLanguage15.382
6PHPLanguage13.6218
7JavascriptLanguage13

We will now write some SQL that retrieves a rating of each language or database system from the intRating column of the tblTechnologies table you see above. The thing to note: we want to have the value we retrieve to be consistently in the format of ##.#, i.e., having precision limited to only one number after the decimal.

1
2
3
4
5
SELECT
    txtTechnology
    , ROUND(intRating, 1) AS intRating_rounded
FROM
    tblTechnologies

Analysis of the SQL above:

  • SELECT: We chose two columns to retrieve from our tblTechnologies table; txtTechnology and intRating_rounded.
  • ROUND: This tells CockroachDB to take the number in the intRating column, round it up to 1 decimal, and name the result AS intRating_round.
  • FROM: For the above commands, use the table called “tblTechnologies” as the source of our query data.

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

txtTechnologyintRating_rounded
Java5.3
Mongo16.2
Postgres26.2
CockroachDB18.3
Python15.4
PHP13.6
Javascript13.0

Notice in the row where the txtTechnology column has a value of “Python”, how the Round() function turned 15.382 into 15.4? That’s because Round looks at the number after our specified 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 1. So 15.38 turns into 15.4 because the 8 here is greater than 5.

What if we used FLOOR instead of ROUND on the same table? Let’s see:

1
2
3
4
5
SELECT
    txtTechnology
    , FLOOR(intRating) AS intRating_floored
FROM
    tblTechnologies
txtTechnologyintRating_floored
Java5
Mongo16
Postgres26
CockroachDB18
Python15
PHP13
Javascript13

Notice how FLOOR gives us no choice about where to cut off; it removes everything after the decimal point, basically delivering an integer.

Back to the ROUND function: What if we were to round, but with a precision of 2 instead of 1? First, let us begin out with our original data:

idTechtxtTechnologytxtCatTechintRating
1JavaLanguage5.335
2MongoNoSQL16.21
3PostgresDatabase26.16
4CockroachDBDatabase18.3
5PythonLanguage15.382
6PHPLanguage13.6218
7JavascriptLanguage13

Now we will modify our query to change the precision we are wanting from the ROUND function, changing the “1” to a “2”:

1
2
3
4
5
SELECT
    txtTechnology
    , ROUND(intRating, 2) AS intRating_round
FROM
    tblTechnologies

Which gives us back the following record set:

txtTechnologyintRating_round
Java5.34
Mongo16.21
Postgres26.16
CockroachDB18.30
Python15.38
PHP13.62
Javascript13.00

Notice what happened when we changed that second parameter? When Cockroach didn’t have numbers to go on, because the original value’s precision wasn’t “deep” enough for a particular row, the SQL engine used a zero value for that decimal position.

Functions similar to ROUND

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 us 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 Cockroach SQL had an INTEGER function, CEILING could be mimic’d like so: INTEGER(ROUND(x,1)).

Ceiling function examples

CEILING(3.8) returns 4

CEILING(5.3) returns 5

Floor function examples

As you saw above, FLOOR 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(9.7) returns 9

FLOOR(9.3) returns 9

Trunc function examples

The TRUNC function does not round, it splices out the original number at the specified precision point supplied to the function. If no parameter for precision is given, the decimal is eliminated, thus creating a whole number. Put another way, “Look at a number and remove everything after the decimal.” And there’s more.

TRUNC(numOriginalNumber, [intPrecision])

If no precision is supplied, zero is assumed.

TRUNC(5.75) returns 5

TRUNC(5.31) returns 5

TRUNC(5.31, 1) returns 5.3

Note: It is important to distinguish between the TRUNC function and the TRUNCATE SQL statement in Cockroach.

Conclusion

In this document we learned how to best use the ROUND function for Cockroach in SQL and explored other similar functions such as CEILING, FLOOR, and TRUNC. Code samples are included.

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.