GREATEST and LEAST #4 and #5 of #100PostgreSQLFunctions

This post is a part of the project #100PostgreSQLFunctions, which aims to explain the meaning of 100 PostgreSQL functions and how to use them. For the full list of functions click here.

GREATEST is a function to select the biggest value in a list. This function can have two or more arguments, each of the list values. The values in the list should be of the same datatype. If the datatypes are different, all values will be converted to the first argument datatype. NULL values in the list are ignored. If the values are strings, this function sorts the strings in alphabetical order and then selects the last one.

Examples:

SELECT
    GREATEST(1, 23, 45);

 greatest
----------
       45
(1 row)


SELECT
    GREATEST('Joe', 'Alex', 'Cory');

 greatest
----------
 Joe
(1 row)


SELECT
    GREATEST('3/3/2019'::date, '3/11/2019'::date, '3/5/2019'::date);

  greatest
------------
 2019-03-11
(1 row)


LEAST is a function to select the smallest value in a list. This function can have two or more arguments, each of the list values. The values in the list should be of the same datatype. If the datatypes are different, all values will be converted to the first argument datatype. NULL values in the list are ignored. If the values are strings, this function sorts the strings in alphabetical order and then selects the first one.

Examples:

SELECT
    LEAST(1, 23, 45);

 least
-------
     1
(1 row)


SELECT
    LEAST('Joe', 'Alex', 'Cory');

 least
-------
 Alex
(1 row)


SELECT
    LEAST('3/3/2019'::date, '3/11/2019'::date, '3/5/2019'::date);

   least 
------------
 2019-03-03
(1 row)