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.
Popular Links
Featured Links
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...