PostgreSQL ABS Function

The ABS() function in PostgreSQL returns the absolute value of a number, converting negative numbers to positive while leaving positive numbers unchanged. It's useful for calculating distances, differences, and magnitudes.

Syntax

ABS(numeric_value)
-- Returns the absolute (non-negative) value

Official Documentation

Example usage

-- Basic absolute value
SELECT ABS(-42) AS result;
-- Result: 42

SELECT ABS(42) AS result;
-- Result: 42

SELECT ABS(-3.14) AS result;
-- Result: 3.14

-- Calculate difference between values
SELECT
    actual_value,
    expected_value,
    ABS(actual_value - expected_value) AS difference
FROM measurements;

-- Find deviation from average
SELECT
    student_name,
    score,
    AVG(score) OVER () AS class_average,
    ABS(score - AVG(score) OVER ()) AS deviation_from_avg
FROM test_scores;

-- Calculate distance between coordinates
SELECT
    ABS(x2 - x1) AS x_distance,
    ABS(y2 - y1) AS y_distance,
    SQRT(POWER(x2 - x1, 2) + POWER(y2 - y1, 2)) AS euclidean_distance
FROM coordinates;

-- Find records with significant variance
SELECT *
FROM inventory
WHERE ABS(actual_count - expected_count) > 10;

-- Calculate absolute change
SELECT
    date,
    price,
    price - LAG(price) OVER (ORDER BY date) AS price_change,
    ABS(price - LAG(price) OVER (ORDER BY date)) AS absolute_change
FROM stock_prices;

-- Temperature difference
SELECT
    city,
    temperature,
    ABS(temperature - 20) AS difference_from_room_temp
FROM weather;

-- Account balance reconciliation
SELECT
    account_id,
    ledger_balance,
    actual_balance,
    ABS(ledger_balance - actual_balance) AS discrepancy
FROM accounts
WHERE ABS(ledger_balance - actual_balance) > 0.01;

-- Calculate age difference
SELECT
    person_a,
    person_b,
    age_a,
    age_b,
    ABS(age_a - age_b) AS age_difference
FROM relationships;

-- Financial calculations
SELECT
    transaction_id,
    amount,
    ABS(amount) AS magnitude,
    CASE
        WHEN amount < 0 THEN 'Debit'
        WHEN amount > 0 THEN 'Credit'
        ELSE 'Zero'
    END AS transaction_type
FROM transactions;

Common issues

1. Not for vectors: ABS works on single numeric values, not arrays or vectors.
2. Data type preservation: Returns same type as input (INTEGER, NUMERIC, etc.).
3. Overflow: While rare, extremely large negative numbers could theoretically overflow.
4. NULL handling: ABS(NULL) returns NULL.

Best practices

1. Use ABS for calculating magnitude of difference without regard to direction.
2. Combine with comparison operators to find values within a tolerance: ABS(a - b) < threshold.
3. Use in distance calculations for one-dimensional measurements.
4. For multi-dimensional distance, combine with POWER and SQRT for Euclidean distance.

Frequently Asked Questions

Q: What does ABS return for zero?
A: ABS(0) returns 0. Zero is neither positive nor negative, so its absolute value is itself.

Q: Can ABS handle NULL values?
A: ABS(NULL) returns NULL. Use COALESCE if you need a default: ABS(COALESCE(value, 0)).

Q: How do I calculate distance between two points?
A: For 1D: ABS(x2 - x1). For 2D: SQRT(POWER(x2-x1, 2) + POWER(y2-y1, 2)). For latitude/longitude, use the PostGIS extension.

Q: Is there a performance difference between ABS and conditional logic?
A: ABS is more efficient than CASE WHEN value < 0 THEN -value ELSE value END. Use the built-in function.

Q: Can I use ABS with INTERVAL types?
A: Yes, but with a special syntax: @interval_value or use ABS with EXTRACT(EPOCH): ABS(EXTRACT(EPOCH FROM interval_value)).