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.