PostgreSQL: GREATEST() and LEAST()

In PostgreSQL, the GREATEST() function returns the largest value from the specified values and the LEAST() functions returns the smallest values from specified values. Both the functions take any number of arguments.

All the arguments or expressions must be convertible to a common data type, otherwise, it will raise an error.

GREATEST(<argument1>, <argument2>,...)
LEAST(<argument1>, <argument2>,...)

The following demonstrates the GREATEST() and LEAST() functions.

SELECT GREATEST(25, 6, 7, 10, 20, 54);  --  returns 54
SELECT LEAST(25, 6, 7, 10, 20, 54);  --  returns 6

They work on the character data types also.

SELECT GREATEST('D','A', 'B', 'C','d','e','E'); -- returns 'E'
SELECT LEAST('D','A', 'B', 'C','d','e','E','a'); -- returns 'a'

Consider the following Employee table.

Now, the following finds the greatest and least values from first_name and last_name values.

SELECT GREATEST(first_name, last_name) AS LargeValue, LEAST(first_name, last_name) AS LeastValue FROM employee;

Both the functions ignore the NULL values. If all arguments are NULL, then return a NULL.

SELECT GREATEST(6, NULL); -- result: 6
SELECT LEAST(6, NULL); -- result: 6