PostgreSQL Math Functions

Common Math Functions

SELECT ROUND(15.7);

SELECT ABS(-42);

SELECT SQRT(16);

SELECT RANDOM();

🔹 ABS() - Absolute Value

Return the absolute (positive) value of a number. ABS removes the negative sign, converting any negative number to positive while leaving positive numbers unchanged. Useful for calculating distances or differences.

-- Get absolute value
SELECT ABS(-42);
-- Returns: 42

SELECT ABS(42);
-- Returns: 42

-- Calculate price differences
SELECT 
    product_name,
    price,
    target_price,
    ABS(price - target_price) AS price_difference
FROM products;
                            

🔹 ROUND(), CEIL(), FLOOR()

Round numbers to specified precision or to nearest integer. ROUND rounds to nearest value, CEIL always rounds up, and FLOOR always rounds down. Essential for financial calculations and data presentation.

-- Round to nearest integer
SELECT ROUND(15.7);
-- Returns: 16

-- Round to 2 decimal places
SELECT ROUND(3.14159, 2);
-- Returns: 3.14

-- Always round up
SELECT CEIL(15.2);
-- Returns: 16

-- Always round down
SELECT FLOOR(15.9);
-- Returns: 15

-- Calculate totals
SELECT 
    order_id,
    ROUND(total * 1.08, 2) AS total_with_tax,
    CEIL(total) AS rounded_up_total
FROM orders;
                            

🔹 POWER() and SQRT()

Calculate powers and square roots. POWER raises a number to an exponent, while SQRT finds the square root. These functions are fundamental for scientific calculations, statistics, and geometric computations.

-- Calculate power
SELECT POWER(2, 3);
-- Returns: 8 (2^3)

SELECT POWER(5, 2);
-- Returns: 25 (5^2)

-- Calculate square root
SELECT SQRT(16);
-- Returns: 4

SELECT SQRT(25);
-- Returns: 5

-- Calculate area and distance
SELECT 
    POWER(radius, 2) * 3.14159 AS circle_area,
    SQRT(POWER(x2 - x1, 2) + POWER(y2 - y1, 2)) AS distance
FROM coordinates;
                            

🔹 MOD() - Modulo

Return the remainder after division. MOD is perfect for finding even/odd numbers, creating cyclic patterns, or distributing items evenly. The result has the same sign as the dividend.

-- Get remainder
SELECT MOD(10, 3);
-- Returns: 1

SELECT MOD(15, 4);
-- Returns: 3

-- Find even numbers
SELECT * FROM numbers
WHERE MOD(value, 2) = 0;

-- Find odd numbers
SELECT * FROM numbers
WHERE MOD(value, 2) = 1;

-- Distribute items
SELECT 
    order_id,
    MOD(order_id, 5) AS assigned_warehouse
FROM orders;
                            

🔹 TRUNC() - Truncate

Truncate a number to specified decimal places without rounding. Unlike ROUND, TRUNC simply removes digits after the specified position. Useful when you need to cut off precision without rounding effects.

-- Truncate to integer
SELECT TRUNC(15.7);
-- Returns: 15

-- Truncate to 2 decimals
SELECT TRUNC(3.14159, 2);
-- Returns: 3.14

-- Compare with ROUND
SELECT 
    value,
    ROUND(value, 1) AS rounded,
    TRUNC(value, 1) AS truncated
FROM measurements;
                            

🔹 RANDOM()

Generate a random number between 0 and 1. RANDOM is useful for sampling data, creating test data, or implementing randomized features. Multiply and add to get different ranges.

-- Generate random number (0 to 1)
SELECT RANDOM();
-- Returns: 0.547382 (example)

-- Random integer between 1 and 100
SELECT FLOOR(RANDOM() * 100 + 1);

-- Random integer between 10 and 50
SELECT FLOOR(RANDOM() * 41 + 10);

-- Select random rows
SELECT * FROM products
ORDER BY RANDOM()
LIMIT 5;

-- Assign random priorities
SELECT 
    task_id,
    FLOOR(RANDOM() * 5 + 1) AS random_priority
FROM tasks;
                            

🔹 SIGN()

Return the sign of a number as -1, 0, or 1. SIGN indicates whether a number is negative, zero, or positive. Helpful for categorizing values or implementing conditional logic based on number signs.

-- Get sign of number
SELECT SIGN(-42);
-- Returns: -1

SELECT SIGN(0);
-- Returns: 0

SELECT SIGN(42);
-- Returns: 1

-- Categorize values
SELECT 
    account_id,
    balance,
    CASE SIGN(balance)
        WHEN -1 THEN 'Negative'
        WHEN 0 THEN 'Zero'
        WHEN 1 THEN 'Positive'
    END AS balance_status
FROM accounts;
                            

🔹 EXP() and LN()

Calculate exponential and natural logarithm. EXP raises e to a power, while LN finds the natural logarithm. These functions are essential for exponential growth calculations, scientific formulas, and statistical analysis.

-- Exponential (e^x)
SELECT EXP(1);
-- Returns: 2.718281828459045 (e)

SELECT EXP(2);
-- Returns: 7.389...

-- Natural logarithm
SELECT LN(2.718281828459045);
-- Returns: 1

SELECT LN(10);
-- Returns: 2.302...

-- Calculate compound interest
SELECT 
    principal * EXP(rate * years) AS future_value
FROM investments;
                            

🔹 PI()

Return the mathematical constant π (pi). PI provides the precise value of pi for geometric calculations involving circles, spheres, and trigonometry. Essential for area, circumference, and angle calculations.

-- Get PI value
SELECT PI();
-- Returns: 3.141592653589793

-- Calculate circle area
SELECT PI() * POWER(radius, 2) AS area
FROM circles;

-- Calculate circle circumference
SELECT 2 * PI() * radius AS circumference
FROM circles;

-- Example with radius 5
SELECT 
    5 AS radius,
    PI() * POWER(5, 2) AS area,
    2 * PI() * 5 AS circumference;
                            

🔹 GREATEST() and LEAST()

Return the largest or smallest value from a list of values. These functions compare multiple values and return the maximum or minimum. Perfect for finding extremes across columns or comparing multiple options.

-- Get greatest value
SELECT GREATEST(10, 25, 15, 30);
-- Returns: 30

-- Get least value
SELECT LEAST(10, 25, 15, 30);
-- Returns: 10

-- Compare multiple columns
SELECT 
    product_name,
    GREATEST(price_online, price_store, price_wholesale) AS max_price,
    LEAST(price_online, price_store, price_wholesale) AS min_price
FROM products;

-- Find maximum discount
SELECT 
    GREATEST(discount_a, discount_b, discount_c) AS best_discount
FROM promotions;