Postgres Numeric Functions
In this tutorial, we’ll explore the use some popular Postgres numeric functions. We will learn the following functions that are number oriented and non-aggregate in nature:
- ABS function
- FLOOR function
- ROUND function
We’ll also explore the RANDOM function, CEILING function and TRUNC function.
What is the Postgres ABS function?
The Postgres ABS() function returns the absolute value of the parameter submitted. Absolute value of a number is calculated by looking at how far from zero the number is, ignoring if the number is positive or negative. Putting it simply: The ABS function takes away the minus sign from a negative number, making it positive, and makes no change to positive numbers.
Here is a text-based example of how ABS works:
Postgres ABS function syntax
The value can be a field or value that Postgres evaluates as a number. Whatever data type you enter, you get that same data type back. For example:
|Value Input||Value Returned||Type|
Postgres ABS function example(s)
The above query will return the following:
|Yaxis Value||Value Returned|
A use case: we are comparing weekly numbers of estimated growth vs. actual growth where we can’t know if subtracting the estimated number from the actual number will give us a positive or negative number on any given row (week). From the comparison below, we want to know how “off” the estimates were. Here is an example:
|Week||Actual Growth||Estimated Growth||Actual – Est||ABS(Actual – Est)|
See how the column not using the ABS function, labeled “Actual – Est” has a total of -12? Twelve is the correct disparity between actual and estimated growth.
The above data (minus total) can be pulled out of Postgres with an SQL query like:
ORDER BY i_week
Another Postgres ABS use case
What if we want to calculate the accuracy or inaccuracy of the performance estimates of a car industry reviewer so we can decide whether to let him go or give him a promotion?
|Car||Performance Rating||Estimated Performance Rating||(Actual – Est) Inaccuracy||ABS(Actual – Est) Inaccuracy|
|Tesla Model 3||90||85||5||5|
We used the following Postgres query to retrieve the information you see above:
WHERE t_type = 'electric'
Postgres ABS Shortcut
The @ operator acts like ABS:
The Postgres FLOOR function
The Postgres FLOOR() function returns the highest value integer that is equal to or smaller than the supplied number. In other words, it chops off the decimal point.
Postgres Floor function syntax
- Input value: 1.23552135, Output value: 1
- Input value: 4.90257826, Output value: 4
- Input value: 2.53824445, Output value: 2
- Input value: -2.73824445, Output value: -3
- Input value: -2.10824445, Output value: -3
Note: FLOOR() is identical to the INT() function in many programming languages.
Postgres FLOOR function examples
One way we may want to use the Floor function is when generating random numbers for realistic use because PostgreSQL’s RANDOM function returns numbers that are many precision points from the decimal point. So we’ll use that as a learning exercise.
First, let’s see how the FLOOR() and RANDOM() function work together.
- FLOOR(RANDOM()9)*: returns a random integer between (and including) 0 and 8.
- FLOOR(RANDOM()9)+1*: returns a random integer between (and including) 1 and 9.
- FLOOR(RANDOM()(i_largest-i_smallest)+i_smallest)*: returns a random integer between (and including) i_smallest and i_largest.
Postgres FLOOR function exercise
The mission: fill a table with random numbers. We’ll begin by creating a practice table called “tbl_random_abs_test”:
id serial NOT NULL
, i_number int4 NULL DEFAULT 0
, i_random_num int8 NULL DEFAULT 0
, i_times_picked int8 NOT NULL DEFAULT 0
, d_time TIME NULL DEFAULT now()
CREATE UNIQUE INDEX tbl_random_abs_test_id_idx ON public.tbl_random_abs_test USING btree (id);
The Postgres query above should yield a table like this:
(https://gyazo.com/19d647e80894097aba5de18ee2c125ab “Floor function Postgres SQL”)
- id: This is good database design; to create a table with an indexed id field. Quite often you will need a field like this with a unique index, especially in – hopefully normalized – relational databases. In addition, we set the id column to auto-increment, which is another practice we recommend.
- _inumber: This column is so we can easily follow what is going on when we insert and update data in the tbl_random_abs_test Postgres table.
- _i_randomnum: This is the meat of our project. We store the random numbers we create using the PostgreSQL RANDOM() function in this column.
- _i_timespicked: This is the field we will use to store our calculation later when we UPDATE the table using the COUNT() function to how many times any given random number popped up. We’ll use that to scrutinize just how random our numbers are.
- _dtime: This is another column we put in for your future use of this table, in case you decide to do testing to find out speeds of various techniques of using the RANDOM function in your queries. We are currently working with small datasets, but you could easily change the “generate_series()” parameter to generate millions of rows instead of the small number we do here. And if you do that, you can then compare how much time it takes to run each time.
Fill our Postgres table with random values
i AS i_number
generate_series(1,10) AS i;
- (1) INSERT INTO: We instruct PostgreSQL that we are going to insert data into two columns; we named those fields “i_number” and “i_random_num”.
- (2) SELECT: Generate the values to go in the two columns we mentioned above. We’re getting “i” later in our FROM section. The next line, FLOOR(RANDOM()100)* creates random integers between 0 and 100.
- (3) FROM: Uses the _generate_Series(i_start, iend) function to generate 10 numbers in sequence, like so: 1, 2, 3, 4, 5, 6, 7 8, 9, 10. This will also cause our FROM to generate 10 rows, which will be new rows because of the INSERT command.
After running the above SQL, _tbl_random_abstest should have 10 rows and look just like this, except with different numbers in the “i_random_num” column:
(https://gyazo.com/b861f1c6b4a1f08ee3f05edd37ad9630 “Table after insert of Random integers”)
Now, we need to keep in mind we have generated only 10 numbers and really, that’s not enough to be sure just how random our number distribution is. Also, we may or may not have duplicates, and we can tell by eyeballing since the table only has 10 records, so no need to make use of the _i_timespicked column yet. Let’s go ahead then and generate more rows using the same query as before, except changing the line in our Postgres query that says _generateSeries(1,10) AS i; to use “20” instead of “10”.
i AS i_number
generate_Series(1,20) AS i;
Now the table has 30 rows. Look at the table contents. Are there any duplicate numbers in the _i_randomnum column? If not, run that INSERT query again. Keep running it until your resulting data set has at least one duplicate in the _i_randomnum field. You can even set your “generate_series” function to create a few thousand rows. The more you create, the more likely we’ll be to find any patterns that may exist
That’s it for Floor. Now we move on to another important Postgres numeric function; Round.
The Postgres ROUND function
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.
Postgres Round function syntax
Note: the precision parameter is optional. If empty, PostgreSQL assumes zero.
Postgres Round function example
Now we’ll use the ROUND() function for Postgres in an SQL database environment so as to best understand how we can include the ROUND function into our database-based applications. Let’s begin with a sample table called _tbltechnologies:
We’ll now write some SQL that pulls a rating of each technology from the _irating 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…: Specifies two columns to return from our table; t_name_tech and i_rating_round.
- ROUND: This tells PostgreSQL to take the value in the i_rating column, round it up to 1 decimal, and name the result “i_rating_round” with the “AS” clause.
- FROM: For the above statements, use the table called “tbl_technologies” as the source of our Postgres query’s data.
The query above returns the following results:
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.
Let’s experiment some to learn more: If we were to round with a precision of 2 instead of 1?
, ROUND(i_rating, 2) AS i_rating_round
Which gives us back the following results:
Note: when Postgres didn’t have numbers after the decimal to use, because the original value’s precision wasn’t “deep” enough, PostgreSQL used a zero value for that position.
Functions similar to Round and Floor
Before we finish with the ROUND function: there may be situations where you thought the solution might be to use Round() or Floor(), but actually, the Ceiling() or Trunc() functions are better suited to your needs. A brief look at the Ceiling() and Trunc() functions (since we already studied Floor):
Postgres Ceiling function
CEILING(): This rounds the value up to the next higher integer. The decimal is eliminated. Simply put, “Take the value, round up, and make it an integer.” If Postgres SQL had an INTEGER function, CEILING would be like INTEGER(ROUND(x,1)).
Postgres Ceiling function examples
CEILING(2.7) returns 3
CEILING(2.3) returns 2
Postgres Trunc function
The Trunc() function does not round; it cuts the original value at the specified decimal point supplied as the function’s parameter. If no precision parameter is given, the decimal is removed, creating a whole number. Put simply, “Take the value and remove everything after the decimal.”
Postgres Trunc function syntax
If no precision is supplied, zero is assumed.
Postgres Trunc function examples
TRUNC(2.75) returns 2
TRUNC(2.31) returns 2
TRUNC(2.31, 1) returns 2.3
We learned to use some common and important Postgres numeric functions; ABS, FLOOR, and ROUND. Along the way we also learned the basics of the PostgreSQL RANDOM function, CEILING function and TRUNC function. Code samples and images were included.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started