PostgreSQL: Absolute Value

In my work building financial dashboards and inventory systems, I constantly need to measure differences regardless of direction. Whether you’re calculating price variance, tracking deviations from targets, or measuring distance between values, you’ll often care about the magnitude—not whether it’s positive or negative. That’s where PostgreSQL’s ABS() function comes in handy.

The ABS() function strips away the sign and gives you the absolute value—the positive version of any number. It’s one of those simple but powerful tools that makes complex calculations much cleaner.

Quick Answer: Use ABS() Function

To get the absolute value of a number:

SELECT
    value,
    ABS(value) as absolute_value
FROM numbers
WHERE value IS NOT NULL
LIMIT 5;

Returns:

value | absolute_value
------|---------------
-100  | 100
50    | 50
-25   | 25
-7.5  | 7.5
200   | 200

ABS() returns the positive version of any number, whether it started as positive, negative, or zero.

How It Works

Here’s what’s happening under the hood: the ABS() function takes the magnitude of a number—its absolute distance from zero—and returns it as a positive value. If you pass in -100, it returns 100. If you pass in 50, it stays 50. If you pass in 0, it stays 0.

The beauty of ABS() is that it works with any numeric type in PostgreSQL: INTEGER, BIGINT, DECIMAL, NUMERIC, FLOAT, DOUBLE PRECISION—all of them. This means you can use it in calculations with prices, measurements, coordinates, percentages, or any number in your database.

What makes ABS() particularly useful is that it’s a pure mathematical function. It doesn’t round, truncate, or modify your data in any way—it just flips the sign if needed. You can safely nest it inside other calculations, aggregate functions, or conditional logic without worrying about unexpected behavior.

Different Approaches & When to Use Them

Approach 1: Simple ABS for Column Values

The most straightforward use case is when you want to get the absolute value of existing column data:

SELECT
    emp_id,
    salary_adjustment,
    ABS(salary_adjustment) as adjustment_magnitude
FROM salary_changes
ORDER BY adjustment_magnitude DESC;

This is perfect when you’re analyzing data and want to rank changes by their size, regardless of whether they’re increases or decreases. I’ve used this frequently when reviewing which employees got the largest adjustments in either direction.

Approach 2: Use ABS() in Calculations for Variance Analysis

Here’s where ABS() really shines—calculating differences and variances:

SELECT
    employee_id,
    target_sales,
    actual_sales,
    ABS(target_sales - actual_sales) as sales_variance
FROM quarterly_performance
WHERE quarter = 'Q1 2026'
ORDER BY sales_variance DESC;

This isn’t just about finding the difference; you’re finding the magnitude of the difference. Whether someone missed their target by $5,000 or exceeded it by $5,000, you get 5000 either way. This makes it easy to identify your top performers (positive variance) and those needing support (negative variance) by the size of the gap.

Approach 3: Distance Calculations Between Two Points

If you’re working with numeric coordinates or comparing two measurements:

SELECT
    reading_id,
    expected_temperature,
    actual_temperature,
    ABS(expected_temperature - actual_temperature) as temperature_variance
FROM sensor_readings
WHERE ABS(expected_temperature - actual_temperature) > 5;

This is particularly useful in IoT applications, scientific data collection, or quality control systems where you need to flag readings that deviate significantly from expected values.

You technically could use CASE WHEN to replicate ABS():

SELECT
    value,
    CASE WHEN value < 0 THEN -value ELSE value END as manual_abs
FROM numbers;

However, I’d recommend sticking with ABS(). It’s clearer, faster, and less error-prone. The CASE WHEN approach is more verbose and doesn’t communicate your intent as effectively.

Real-World Example: Price Variance Dashboard

Let me walk you through a practical scenario. Imagine you’re tracking product pricing across your e-commerce platform. You have target prices set by management, but actual prices vary by market, promotion, and other factors. You want to identify which products have the biggest pricing discrepancies:

SELECT
    product_id,
    product_name,
    target_price,
    actual_price,
    ABS(target_price - actual_price) as price_variance,
    ROUND(ABS(target_price - actual_price) / target_price * 100, 2) as variance_percent
FROM products
WHERE category = 'Electronics'
ORDER BY variance_percent DESC
LIMIT 20;

This query gives you:

  • The absolute difference in dollars
  • The variance as a percentage
  • Products sorted by the magnitude of variance, regardless of whether prices are higher or lower than target

This is exactly what you’d show to a pricing manager: “Here are the 20 products with the biggest pricing deviations, sorted by how far off they are.”

FAQ

Q: Is there a MINUS function to get the negative version of a number? A: No, but you don’t really need one. You can get the negative version with -ABS(value) or by multiplying by -1: value * -1. The ABS() function is the important one since you can’t easily get absolute values without it.

Q: How does ABS() handle NULL values? A: ABS() returns NULL if you pass in NULL. This is standard SQL behavior. If you want a default value instead, wrap it with COALESCE(): COALESCE(ABS(value), 0).

Q: Can I use ABS() in WHERE clauses to filter data? A: Absolutely! This is really common. For example: WHERE ABS(balance - target) > 100 finds all records where the variance exceeds 100, regardless of direction.

Q: What’s the performance impact of using ABS()? A: It’s negligible. ABS() is a built-in mathematical operation—extremely fast. You can safely use it on millions of rows without performance concerns.

Q: Does ABS() work with all numeric types? A: Yes, it works with INTEGER, BIGINT, SMALLINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION, and MONEY types. It doesn’t work on non-numeric types like strings or dates.

Q: Can I combine ABS() with other functions? A: Definitely. You can nest it with ROUND(), CAST(), GREATEST(), LEAST(), and almost any other function. For example: ROUND(ABS(value), 2) rounds the absolute value to 2 decimal places.

Q: How does ABS() differ from LEAST() and GREATEST()? A: Good question. ABS() gives you the magnitude of a single number. GREATEST() and LEAST() compare multiple values and return the largest or smallest. They solve different problems. Use ABS() for magnitude, GREATEST()/LEAST() for comparisons.

Q: What happens if I use ABS() on the result of a subtraction with dates? A: PostgreSQL’s date arithmetic returns an integer (days). ABS() works fine on this. For example: ABS(CURRENT_DATE - hire_date) gives you the absolute number of days between dates.

Q: Can I use ABS() in GROUP BY and aggregate functions? A: Yes. You can group by ABS(value) if you want to group positive and negative values together, or use it inside aggregates like SUM(ABS(amount)) to sum absolute values.

Common Errors & Solutions

Error: “function abs(bigint) does not exist” This usually means you’re trying to use a function name that PostgreSQL doesn’t recognize. Make sure it’s spelled ABS() (uppercase is fine—PostgreSQL isn’t case-sensitive for function names). If you’ve aliased a column as abs, you might have a naming conflict.

Error: “ABS returns NULL when you expect a number” If your column contains NULL values, ABS(NULL) returns NULL. Filter out NULLs in your WHERE clause: WHERE value IS NOT NULL. Or use COALESCE() to provide a default: COALESCE(ABS(value), 0).

Performance Tips

  • Simple and fast: ABS() is a native function with virtually no performance overhead. Don’t worry about optimization.
  • Use with WHERE clauses: Filtering by ABS(column) > value works efficiently, especially on indexed columns.
  • Combine with indexes: If you frequently filter on calculated absolute values, consider a computed column or partial indexes.
  • Avoid in ORDER BY for large sorts: ORDER BY ABS(column) works fine but creates a computed result set. For very large datasets (millions of rows), ensure your query planner is using indexes effectively—check with EXPLAIN ANALYZE.

Wrapping Up

The ABS() function is one of those simple tools that’s incredibly useful once you start looking for it. Whether you’re measuring price variance, analyzing deviations, calculating distances, or comparing actual-to-target metrics, ABS() makes your queries cleaner and your intent clearer. Use it freely—it’s fast, reliable, and works with any numeric type in PostgreSQL.