How to Use GREATEST and LEAST Functions in PostgreSQL

In PostgreSQL, use the GREATEST function to find the largest value among two or more expressions, and use LEAST to find the smallest. Both functions compare all given arguments and return the maximum or minimum value respectively.

Syntax

The GREATEST and LEAST functions take two or more expressions as arguments and return the maximum or minimum value respectively.

• GREATEST(value1, value2, ...): Returns the largest value.
• LEAST(value1, value2, ...): Returns the smallest value.

All arguments must be of compatible data types.

sql

SELECT GREATEST(value1, value2, ..., valueN) AS max_value,
       LEAST(value1, value2, ..., valueN) AS min_value;

Example

This example shows how to use GREATEST and LEAST to find the highest and lowest scores among three columns for each row.

sql

CREATE TEMP TABLE scores (
  id SERIAL PRIMARY KEY,
  score1 INT,
  score2 INT,
  score3 INT
);

INSERT INTO scores (score1, score2, score3) VALUES
  (85, 90, 78),
  (92, 88, 95),
  (70, 75, 80);

SELECT id, score1, score2, score3,
       GREATEST(score1, score2, score3) AS highest_score,
       LEAST(score1, score2, score3) AS lowest_score
FROM scores
ORDER BY id;

Output

 id | score1 | score2 | score3 | highest_score | lowest_score 
----+--------+--------+--------+---------------+--------------
  1 |     85 |     90 |     78 |            90 |           78
  2 |     92 |     88 |     95 |            95 |           88
  3 |     70 |     75 |     80 |            80 |           70

Common Pitfalls

Common mistakes when using GREATEST and LEAST include:

• Passing NULL values: If any argument is NULL, the result is NULL. Use COALESCE to handle NULLs.
• Mixing incompatible data types: All arguments should be of compatible types to avoid errors.
• Using these functions with no arguments: They require at least two arguments.

sql

/* Wrong: NULL causes NULL result */
SELECT GREATEST(10, NULL, 5) AS result_wrong;

/* Right: Use COALESCE to replace NULL */
SELECT GREATEST(10, COALESCE(NULL, 0), 5) AS result_right;

Output

 result_wrong 
--------------
 
(1 row)

 result_right 
--------------
           10
(1 row)

Quick Reference

Function	Purpose	Example	Notes
GREATEST	Returns the largest value among arguments	GREATEST(3, 7, 5) → 7	Returns NULL if any argument is NULL
LEAST	Returns the smallest value among arguments	LEAST(3, 7, 5) → 3	Returns NULL if any argument is NULL

Key Takeaways

Use GREATEST to find the maximum value among multiple expressions in PostgreSQL.

Use LEAST to find the minimum value among multiple expressions in PostgreSQL.

Both functions return NULL if any argument is NULL unless handled with COALESCE.

All arguments must be compatible data types to avoid errors.

You must provide at least two arguments to these functions.