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:

Image from Gyazo

Here is a more textual example of how ABS works:

SELECT ABS(i_Yaxis) FROM Data1

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:

SELECT ID, i_week, i_growth_actual, i_growth_estimated, ABS(i_growth_actual - i_growth_estimated)
FROM growth_plants_weekly
ORDER BY i_week

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?

CarActual Performance RatingEstimated Performance Rating(Actual – Est) InaccuracyABS(Actual – Est) Inaccuracy
Tesla Model 3908555
Porsche Taycan8090-1010
Toyota Prius707000
Nissan Leaf6065-55
Total Inaccuracy:-1020

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

SELECT ID, t_name_car, i_performance_actual, i_performance_estimated, ABS(i_performance_actual - i_performance_estimated)
FROM cars
WHERE t_type = 'electric'

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.

EmployeeSales ActualSales ReportedInaccurate Lie Count (Actual – Reported)Accurate Lie Count: ABS(Actual – Reported)
Bob92000800001200012000
Tina980009800000
Fred9100092000-10001000
Tina8500095000-1000010000
Total Lying:100023000

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

SELECT ID, t_name_first, t_name_last, c_sales_actual, c_sales_reported, ABS(c_sales_actual - c_sales_reported)
FROM employees
WHERE ID_department = 3

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:

SELECT @ -88

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?

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.