Postgres abs() function

The Postgres abs() function is used to compute the absolute value of a number. The absolute value is the non-negative value of a number without regard to its sign.

It's useful in multiple scenarios when working with numbers, such as calculating distances, comparing magnitudes regardless of direction, or ensuring non-negative values in financial calculations.

Function signature

The abs() function has a simple form:

abs(number) -> number

• number: The input value for which you want to calculate the absolute value. It can be of any numeric data type - integer, floating-point, or decimal.

Example usage

Consider a table transactions with an amount column that contains both positive (deposits) and negative (withdrawals) values. We can use abs() to order the transactions by their magnitude.

WITH transactions(id, amount) AS (
  VALUES
    (1, 100.50),
    (2, -75.25),
    (3, 200.00),
    (4, -150.75)
)
SELECT id, amount
FROM transactions
ORDER BY abs(amount) DESC;

This query retrieves the transaction IDs and amounts, ordering them by the absolute value of the amount, in descending order.

id | amount
----+---------
  3 |  200.00
  4 | -150.75
  1 |  100.50
  2 |  -75.25
(4 rows)

Other examples

Using abs() for distance calculations

The abs() function is also frequently used for distance calculations, where the direction is not relevant. Suppose we have a table of geographical coordinates and we want to find points within a certain range of a reference point.

WITH locations(name, latitude, longitude) AS (
  VALUES
    ('Point A', 40.7128, -74.0060),
    ('Point B', 40.7484, -73.9857),
    ('Point C', 41.6892, -74.0445),
    ('Reference', 40.7300, -73.9950)
)
SELECT
  name,
  abs(latitude - 40.7300) AS lat_diff,
  abs(longitude - (-73.9950)) AS long_diff
FROM locations
WHERE
  abs(latitude - 40.7300) <= 0.05 AND
  abs(longitude - (-73.9950)) <= 0.05;

This query finds all points within 0.05 degrees (approximately 5.5 km) of the reference point (40.7300, -73.9950) in both latitude and longitude.

name    | lat_diff | long_diff
-----------+----------+-----------
 Point A   |   0.0172 |    0.0110
 Point B   |   0.0184 |    0.0093
 Reference |   0.0000 |    0.0000
(4 rows)

Combining abs() with other functions

We can combine abs() with other functions for more complex calculations. For example, to measure the percentage discrepancy between forecasted and actual sales, we can use abs() to calculate the size of the difference and then divide it by the forecasted value.

WITH sales_data(product, forecast, actual) AS (
  VALUES
    ('Product A', 1000, 1100),
    ('Product B', 500, 450),
    ('Product C', 750, 725),
    ('Product D', 300, 400)
)
SELECT
  product,
  forecast,
  actual,
  round(abs(actual - forecast) / forecast::numeric * 100, 2) AS percentage_difference
FROM sales_data
ORDER BY percentage_difference DESC;

This query orders the products by the percentage difference between the forecasted and actual sales.

product  | forecast | actual | percentage_difference
-----------+----------+--------+-----------------------
 Product D |      300 |    400 |                 33.33
 Product A |     1000 |   1100 |                 10.00
 Product B |      500 |    450 |                 10.00
 Product C |      750 |    725 |                  3.33
(4 rows)

Additional considerations

Performance implications

The abs() function is pretty quick, as it's a simple mathematical operation. However, if you frequently filter or join a large dataset based on absolute values, consider creating a functional index using abs() to speed up queries.

Alternative functions and operators

• The @ operator: Postgres provides the @ operator as an alternative to the abs() function. It performs the same operation (calculating the absolute value) and can be used interchangeably with abs(). For example, @ -5 is equivalent to abs(-5).

Resources

• PostgreSQL documentation: Mathematical Functions and Operators
• PostgreSQL documentation: Numeric Types