PostgreSQL GREATEST and LEAST Functions

The GREATEST() and LEAST() functions in PostgreSQL return the largest and smallest values respectively from a list of arguments. Unlike MAX and MIN aggregate functions, these work across columns in a single row.

Syntax

-- Return largest value from arguments
GREATEST(value1, value2, ..., valueN)

-- Return smallest value from arguments
LEAST(value1, value2, ..., valueN)

Official Documentation

Example usage

-- Find greatest of three numbers
SELECT GREATEST(10, 20, 5) AS max_value;
-- Result: 20

-- Find least of three numbers
SELECT LEAST(10, 20, 5) AS min_value;
-- Result: 5

-- Compare column values
SELECT
    product_name,
    price,
    sale_price,
    clearance_price,
    LEAST(price, sale_price, clearance_price) AS best_price
FROM products;

-- Set minimum value constraint
SELECT
    employee_name,
    GREATEST(salary, 50000) AS adjusted_salary
FROM employees;
-- Ensures minimum salary of $50,000

-- Set maximum value constraint
SELECT
    product_name,
    LEAST(discount, 0.50) AS capped_discount
FROM products;
-- Caps discount at 50%

-- Compare dates
SELECT
    GREATEST(created_at, updated_at, published_at) AS most_recent_date
FROM articles;

-- Find earliest date
SELECT
    LEAST(order_date, ship_date, delivery_date) AS earliest_date
FROM shipments;

-- Handle NULL values (returns NULL if any argument is NULL)
SELECT
    GREATEST(10, NULL, 30) AS result;
-- Result: NULL

-- Use COALESCE to handle NULLs
SELECT
    GREATEST(
        COALESCE(price, 0),
        COALESCE(sale_price, 0),
        COALESCE(msrp, 0)
    ) AS max_price
FROM products;

-- Clamp value to range
SELECT
    value,
    LEAST(GREATEST(value, 0), 100) AS clamped_value
FROM measurements;
-- Clamps value between 0 and 100

-- Compare text values (alphabetical)
SELECT GREATEST('apple', 'banana', 'cherry') AS last_alphabetically;
-- Result: 'cherry'

-- Calculate effective price
SELECT
    product_name,
    regular_price,
    member_price,
    CASE
        WHEN is_member THEN LEAST(regular_price, member_price)
        ELSE regular_price
    END AS effective_price
FROM products;

-- Find latest timestamp across columns
SELECT
    order_id,
    GREATEST(
        COALESCE(created_at, '1970-01-01'::TIMESTAMP),
        COALESCE(processed_at, '1970-01-01'::TIMESTAMP),
        COALESCE(shipped_at, '1970-01-01'::TIMESTAMP)
    ) AS latest_activity
FROM orders;

-- Default value fallback
SELECT
    COALESCE(user_setting, LEAST(system_default, 100)) AS final_setting
FROM settings;

-- Multiple columns comparison
SELECT
    student_name,
    GREATEST(test1_score, test2_score, test3_score) AS highest_score,
    LEAST(test1_score, test2_score, test3_score) AS lowest_score
FROM test_results;

-- Calculate priority
SELECT
    task_name,
    GREATEST(
        urgency_score * 0.4,
        importance_score * 0.3,
        deadline_score * 0.3
    ) AS priority_score
FROM tasks;

Common issues

1. NULL handling: If any argument is NULL, result is NULL. Use COALESCE to provide defaults.
2. Type compatibility: All arguments must be comparable types.
3. Not aggregates: GREATEST/LEAST work on single rows, not across rows like MAX/MIN.
4. String comparison: Text values compared alphabetically based on collation.

Best practices

1. Use COALESCE to handle NULL values before comparing.
2. For finding max/min across rows, use MAX/MIN aggregate functions instead.
3. Combine with LEAST/GREATEST for value clamping: LEAST(GREATEST(value, min), max).
4. Use for ensuring minimum/maximum constraints on calculated values.

Frequently Asked Questions

Q: What's the difference between GREATEST and MAX?
A: GREATEST compares values across columns in a single row: GREATEST(col1, col2, col3). MAX is an aggregate function that finds the maximum across multiple rows: MAX(col1).

Q: How does GREATEST handle NULL values?
A: If any argument is NULL, GREATEST returns NULL. Use COALESCE to provide default values: GREATEST(COALESCE(col1, 0), COALESCE(col2, 0)).

Q: Can I use GREATEST with dates and timestamps?
A: Yes, GREATEST and LEAST work with dates, timestamps, and any comparable type. They return the chronologically latest (GREATEST) or earliest (LEAST) value.

Q: How do I clamp a value to a range?
A: Use nested calls: LEAST(GREATEST(value, min_value), max_value). This ensures value is between min_value and max_value.

Q: Can GREATEST compare text values?
A: Yes, text values are compared alphabetically: GREATEST('apple', 'banana') returns 'banana'. The comparison follows the database collation settings.