PostgreSQL: GREATEST and LEAST Functions

When I’m building commission calculations, handling multiple price lists, or finding the best/worst performance among multiple metrics, I constantly need to compare values across columns in the same row. The challenge is that MIN() and MAX() work on rows in groups, not across columns in individual rows. That’s where GREATEST() and LEAST() come in—they solve a different problem than aggregate functions.

These functions are deceptively powerful. They’re not just for finding maximums and minimums; you can use them for clamping values, tier selection, and handling fallback logic. Once you start using them, you’ll find use cases everywhere.

Quick Answer: Use GREATEST() and LEAST()

To find min/max across columns:

SELECT
    product_id,
    price1,
    price2,
    price3,
    GREATEST(price1, price2, price3) as highest_price,
    LEAST(price1, price2, price3) as lowest_price
FROM products
LIMIT 5;

Returns:

product_id | price1 | price2 | price3 | highest_price | lowest_price
------------|--------|--------|--------|---------------|--------------
1          | 100    | 95     | 110    | 110           | 95
2          | 50     | 60     | 45     | 60            | 45

GREATEST() returns the highest value, LEAST() returns the lowest value. Both work across any number of columns or expressions.

How It Works

GREATEST() and LEAST() compare all values you pass in and return a single result—the maximum or minimum, respectively. They’re row-level functions, meaning they work on a single row at a time, not across an entire result set like aggregate MIN()/MAX().

The key difference: aggregate MIN() and MAX() work on multiple rows and group results. GREATEST() and LEAST() work on multiple columns or values within a single row and return the result for that row. You can compare numbers, dates, text—any comparable values.

They also handle NULL intelligently: if any value is NULL, GREATEST() and LEAST() ignore it and compare the non-NULL values. If all values are NULL, the result is NULL.

Different Approaches & When to Use Them

Approach 1: Compare Multiple Columns (Most Common)

Comparing different price columns, performance metrics, or any multiple values from the same row:

SELECT
    vendor_id,
    vendor_name,
    supplier_price,
    wholesale_price,
    retailer_price,
    GREATEST(supplier_price, wholesale_price, retailer_price) as best_price_available,
    LEAST(supplier_price, wholesale_price, retailer_price) as worst_price_option
FROM vendors
WHERE supplier_price IS NOT NULL
ORDER BY best_price_available;

I use this constantly when comparing multiple price lists or vendor quotes. You want to know which vendor has the best deal and which has the worst. GREATEST() gives you the highest, LEAST() gives you the lowest.

Approach 2: Clamp Values to a Range

Using GREATEST() and LEAST() together to enforce minimum and maximum bounds:

SELECT
    order_id,
    discount_requested,
    -- Clamp discount between 5% and 25%
    GREATEST(LEAST(discount_requested, 0.25), 0.05) as final_discount,
    product_price,
    ROUND(product_price * (1 - GREATEST(LEAST(discount_requested, 0.25), 0.05)), 2) as final_price
FROM orders
WHERE discount_requested > 0;

This is powerful. You’re saying: “The discount is at least 5% and at most 25%.” If someone requests 2%, they get 5% (minimum enforced). If they request 40%, they get 25% (maximum enforced). Anything in between passes through unchanged.

Approach 3: Select the Best Option with Fallback

Using GREATEST() to pick the highest value among options, including fallbacks:

SELECT
    emp_id,
    performance_bonus,
    referral_bonus,
    loyalty_bonus,
    GREATEST(
        performance_bonus,
        referral_bonus,
        loyalty_bonus,
        0  -- Fallback: at least 0 if all are NULL/negative
    ) as final_bonus
FROM employees;

This ensures an employee gets the highest applicable bonus, with a fallback to 0 if nothing applies. It’s cleaner than nested CASE WHEN statements.

Approach 4: Handle Date Ranges

You can use GREATEST() and LEAST() with dates too:

SELECT
    project_id,
    milestone1_date,
    milestone2_date,
    milestone3_date,
    LEAST(milestone1_date, milestone2_date, milestone3_date) as earliest_milestone,
    GREATEST(milestone1_date, milestone2_date, milestone3_date) as latest_milestone,
    GREATEST(milestone1_date, milestone2_date, milestone3_date) -
    LEAST(milestone1_date, milestone2_date, milestone3_date) as project_span_days
FROM projects;

Find the earliest start and latest completion across multiple milestones. Then calculate how long the overall project spans.

Approach 5: Multi-Factor Scoring

Combining with expressions to build sophisticated selection logic:

SELECT
    employee_id,
    base_salary,
    years_experience,
    performance_rating,
    -- Select the highest result from multiple calculation methods
    GREATEST(
        base_salary * 1.1,  -- 10% raise
        50000,              -- Minimum absolute
        base_salary + (years_experience * 1000)  -- Experience-based increase
    ) as recommended_new_salary
FROM employees
WHERE performance_rating >= 3;

You’re comparing three different calculation approaches and picking the one that’s best for the employee. This is real-world compensation logic.

Real-World Example: Commission with Multiple Tiers

Let me walk you through a practical scenario. You’re calculating commissions based on multiple factors—experience tier, sales volume, and a base guarantee:

SELECT
    emp_id,
    emp_name,
    sale_amount,
    years_experience,
    -- Base tier commission
    sale_amount * 0.01 as base_commission,
    -- Experience bonus
    sale_amount * (0.01 + (years_experience * 0.002)) as experience_commission,
    -- Volume bonus
    CASE
        WHEN sale_amount >= 100000 THEN sale_amount * 0.04
        WHEN sale_amount >= 50000 THEN sale_amount * 0.03
        ELSE sale_amount * 0.02
    END as volume_commission,
    -- Guaranteed minimum
    500 as minimum_commission,
    -- Final: the best of all options
    GREATEST(
        sale_amount * 0.01,
        sale_amount * (0.01 + (years_experience * 0.002)),
        CASE
            WHEN sale_amount >= 100000 THEN sale_amount * 0.04
            WHEN sale_amount >= 50000 THEN sale_amount * 0.03
            ELSE sale_amount * 0.02
        END,
        500
    ) as final_commission
FROM employees
WHERE status = 'active'
ORDER BY final_commission DESC;

Here you’re calculating multiple commission approaches—base tier, experience-based, volume-based, and a guaranteed minimum. Then you use GREATEST() to ensure the employee gets the best one. This is exactly how real commission systems work.

FAQ

Q: Can I use GREATEST() and LEAST() with NULL values? A: Yes. NULL is ignored in the comparison. GREATEST(10, NULL, 5) returns 10, LEAST(10, NULL, 5) returns 5. If all values are NULL, the result is NULL.

Q: How many values can I compare? A: Unlimited. GREATEST(col1, col2, col3, col4, col5, col6, ...) works fine. There’s no practical limit.

Q: What’s the performance impact? A: Negligible. These are native, optimized operations. Compare thousands of values per row without concern.

Q: How is this different from aggregate MIN() and MAX()? A: MIN() and MAX() are aggregates—they work across rows and group results. GREATEST() and LEAST() work on columns within a single row. You can’t use MIN() to compare three price columns in one row; that’s what GREATEST() does.

Q: Can I use these with text strings? A: Yes, they compare text alphabetically. GREATEST('apple', 'banana', 'cherry') returns ‘cherry’. Useful for finding the last/first alphabetically.

Q: Can I nest GREATEST and LEAST together? A: Absolutely. GREATEST(LEAST(value, max), min) clamps a value between min and max. This is a common pattern.

Q: Do I have to use the same data type for all values? A: No, PostgreSQL will try to convert compatible types. GREATEST(10, 10.5, 5) works fine. But comparing incompatible types (number vs. text) will error.

Q: Can I use these in WHERE clauses? A: Yes. WHERE GREATEST(col1, col2) > 100 filters rows where the highest value exceeds 100. Completely valid.

Q: What if I want to know which column had the maximum value, not just the value? A: Use CASE WHEN: CASE WHEN col1 >= col2 AND col1 >= col3 THEN 'col1' .... Or use GREATEST() to get the value, then identify which column it came from separately.

Q: Performance: should I avoid GREATEST/LEAST in large queries? A: No, use them freely. They’re fast. Only avoid if you’re comparing hundreds of columns—that’s usually a data model issue, not a function issue.

Common Errors & Solutions

Error: “Comparing different data types” You’re mixing incompatible types like numbers and text. Ensure all values being compared are the same type or explicitly cast them: GREATEST(CAST(col1 AS INTEGER), col2).

Error: “Getting NULL when I expect a value” All input values are probably NULL. Add a non-NULL fallback: GREATEST(col1, col2, 0) ensures at least 0 if both cols are NULL.

Error: “Unexpected ordering with text strings” Remember that text comparison is alphabetical, not numeric. GREATEST('2', '10') returns ‘2’ because ‘2’ comes after ‘10’ alphabetically. Cast to numbers if needed: GREATEST('2'::INTEGER, '10'::INTEGER).

Performance Tips

  • Very fast: These are native functions with no overhead. Use freely.
  • In complex expressions: You can nest them safely: GREATEST(value1 * 100, value2, LEAST(value3, max)). PostgreSQL optimizes the entire expression.
  • With indexes: Filtering WHERE GREATEST(col1, col2) > value works, but won’t use indexes on the columns. If you filter this way frequently, create a computed column.
  • Alternative to CASE: When you’re picking the maximum among expressions, GREATEST() is often faster and more readable than nested CASE WHEN.

Wrapping Up

GREATEST() and LEAST() are your tools for comparing values across columns in a single row. Use them for finding highs and lows, clamping values to ranges, selecting the best option among alternatives, and building sophisticated tier selection logic. They’re fast, reliable, and handle NULL values gracefully. Once you start thinking in terms of “I need the maximum across these columns,” you’ll find these functions solve problems elegantly.