Why use Postgres ABS function in SQL

Introduction

In this article, we will talk the PostgreSQL ABS function from three perspectives, including: – What? From a high level, what does the ABS function do? – Why? In what circumstances do we need a function like this? – How? How do we use this function in our SQL?

What is the ABS function? How does it work?

The PostgreSQL ABS function returns the absolute value of numbers. Absolute value of a number is how far from zero our number is, no matter the number’s positive/negative sign. Another way to put it: ABS removes the minus sign from a negative number, making it positive, and does nothing to positive numbers. The ABS() function has been available in versions of Postgres since version 8.4.

Here is a graphical representation of how the ABS function turns both negative x values and positive x values into positives for Y, which, when we use the formula Y = ABS(X), draws both lines on the top (positive) side of the graph:

Here is a more textual example of how ABS works:

The above SQL command will return the following results: | i_Yasis Value | Value Returned | |————–:|—————:| | abs(16) | 16 | | abs(8) | 8 | | abs(0) | 0 | | abs(-8) | 8 | | abs(-16) | 16 |

Why use the ABS function?

Real-world use case: plant growth

One use case would be if we are comparing weekly numbers of estimated plant growth vs. actual planet growth in millimeters where we can’t know if subtracting estimated growth from actual growth will give us a positive or negative number on any given week (row). From the comparison below, we want to know how “off” or inaccurate the estimates were, so we can force feed massive amounts of raw kale to the offending botanist. Here is an example: | Week | Actual Growth | Estimated Growth | Actual – Est | ABS(Actual – Est) | |——:|————–:|—————–:|————–:|——————-:| | 1 | 120 | 135 | -15 | 15 | | 2 | 128 | 126 | 2 | 2 | | 3 | 121 | 123 | -2 | 2 | | 4 | 119 | 118 | 1 | 1 | | | | Total: | -14 | 20 |

Note how the column not using the ABS function, labeled Actual – Est has an incorrect total of -14? Twenty is the correct total disparity between actual and estimated growth.

The above data (minus total) can be pulled out of Postgres with an SQL statement like:

Another use case: electric automobile performance comparison

What if we want to calculate the total accuracy or inaccuracy of the performance estimates of a car industry journalist so we can decide whether to give him a raise or fire him?

The above data (minus total) would be pulled out of PostgreSQL using an SQL statement like this:

Another use case: count total lying about sales in the department

At SellingIceToEskimos.com, the CFO wants to see a list of salespeople, ranked by who lies the most about their sales numbers. He also wants to see a total of just how much money is being lied about in the sales department. A bonus to be gained from this kind of report is that our curious CFO will also find out who is embezzling.

The above data (minus total) would be pulled out of Postgres with an SQL statement like the following:

How to use the ABS function?

Syntax: ABS(number input) The number input can be a field or value that Postgres evaluates as being a number. Whatever data type you enter, that is the data type you get back. For example: | Number Input | Number Returned | Type Returned | |————-:|—————-:|—————| | 1 | 1 | Integer | | -1 | 1 | Integer | | 1.5 | 1.5 | Numeric | | -1.5 | 1.5 | Numeric |

Postgres Shortcut: The @ operator acts just like ABS. See the following example:

Returns 88

Conclusion

In this article we learned how to use the ABS() function in PostgreSQL. We also explored some real world examples of why one may want to use the ABS function. Did you see the pattern in our examples of eliminating negative results from comparisons so that we could get an accurate reading of totals?