SUMMARY: This article discusses the conditional expressions GREATEST and LEAST in PostgreSQL and gives some examples of their usage.

1. GREATEST

2. LEAST

 

In this post, we are going to look at the functions GREATEST and LEAST in PostgreSQL along with examples.

GREATEST and LEAST functions are also referred as conditional expressions.

 

GREATEST

The GREATEST  function returns the “greatest” or “largest” value from the list of expressions.

Syntax 

GREATEST ( value_1, [value_n] )

value_1 is the first expression or value.

value_n is one or more values (separated by comma).

Examples

The query will return 5, as it is the largest of the values:

postgres=# select greatest(1,2,3,4,5);

greatest 

----------

                 5

(1 row)

 

The sequence or order of the values doesn’t matter. The function will still return the value that is largest:

postgres=# select greatest(5,3,2,4,1);

 greatest 

----------

             5

(1 row)

 

In this example of user-defined timestamps, the most recent date will be returned:

postgres=# select greatest('1970-12-10','2000-11-11','2019-01-01');

greatest  

 ------------

2019-01-01

(1 row)

 

GREATEST can be used with system-date  functions:

  postgres=# select GREATEST('2030-02-20',now());

          greatest          

--------------------------

 2030-02-20 00:00:00+05:30

  (1 row)

 

It can be used as a nested function: 

postgres=# select greatest(1,2,greatest(3,4,5));

greatest 

----------

5

(1 row)

 

It can be used for character comparisons. The character comparison depends on the corresponding character set value:

 

  • This comparison will return ‘c’:

  

postgres=# select GREATEST('a','b','c');

          greatest

---------

           c

        (1 row)

 

  • This comparison will return  ‘cc’:
postgres=# select GREATEST('ca','cb','cc');

greatest 

 ----------

 cc

 (1 row)

 

NULL values are ignored while comparing; the result will depend on the rest of the available values in the list:

     postgres=# select GREATEST(1,null,5);

    greatest 

   ---------

             5

   (1 row)

 

It can be used with aggregate functions:

      postgres=# select sum(greatest(1,23,4,5));

 sum 

 -----

  23

(1 row)

 

LEAST 

The LEAST function returns the “least” or “smallest” value from the list of expressions.

Syntax

LEAST  ( value_1, [value_n] )

value_1 is the first expression or value. 

value_n is one or more values (separated by commas).  

Examples

The query will return 1, as it is the smallest of the values:

postgres=# select least(1,2,3,3,5);

  least 

-------

      1

(1 row)

 

Like with GREATEST, the character comparison depends on the corresponding character set value:

  • This comparison will return ‘a’:
postgres=# select least('a','b','c','aa');

least 

-------

a

(1 row)

 

  • This comparison will return  ‘aa’: 
postgres=# select least('ac','b','c','aa');

least 

  -------

aa

(1 row)

 

This timestamp comparison will return  ‘1970-12-10’ because it is the earliest (oldest) date of  all the mentioned values:

               postgres=#  select least('1970-12-10','2000-11-11','2019-01-01');

 least    

 ------------

1970-12-10

(1 row)

 

NULL values are ignored so it will return the smallest/least of all the remaining values:  

    postgres=# select least (1,null,5);

      least 

     -------

        1

    (1 row)

 

It can be used with aggregate functions:

      postgres=# select sum(least (1,null,5));

       sum 

       -----

          1 

     (1 row)

 

It can be used with negatives. This comparison will return ’-2’, because it is the smallest of the values:

postgres=# select least (1,-1,-2,2);

 least 

-------

    -2

(1 row)

 

Please refer to the PostgreSQL community documentation for more details: 

https://www.postgresql.org/docs/11/functions-conditional.html.