In PostgreSQL, the GREATEST() and LEAST() are built-in functions used to get the biggest and smallest value from the given data, respectively. The GREATEST() and LEAST() functions are very handy for any data analyst or developer working with PostgreSQL. No matter what the case is, these functions are equally effective in analyzing and manipulating numerical and textual data.
This blog post will teach you how to use the GREATEST(), and LEAST() functions in Postgres via practical examples. So, let’s start!
GREATEST() and LEAST() Functions in PostgreSQL
GREATEST() and LEAST() functions in Postgres offer more than just finding the greatest or least value. These functions can compare multiple values at once, making it easy to find the maximum or minimum value among a group of values. These functions provide equally effective results for large sets of data.
Syntax of GREATEST() Function
The following syntax is used to work with the GREATEST() function in Postgres:
GREATEST(val_1, val_2, val_3, …., val_n);
The above snippet shows that the GREATEST() function accepts “n” arguments, retrieving the largest value among them.
Syntax of LEAST() Function
Use the following syntax to implement the LEAST() function in Postgres:
LEAST(val_1, val_2, val_3, …., val_n);
The LEAST() function accepts “n” arguments and retrieves the smallest value among them.
Let’s implement these functions practically!
Example 1: How to Use the GREATEST() Function With Numeric Data?
Let’s learn how the GREATEST() function works on numeric data:
SELECT GREATEST(-612, 127, 481, 620, -767);
The GREATEST() function retrieves the largest integer.
Example 2: How to Use the GREATEST() Function With TEXT Data?
Execute the below code to comprehend the working of the GREATEST() function on alphabetic data:
SELECT GREATEST('ALEX', 'Bob', 'John', 'Seth', 'Joseph');
The GREATEST() function retrieves the greatest value concerning alphabetic order/sequence.
Example 3: How to Use the LEAST() Function With Numeric Data?
The following code snippet shows how the LEAST() function works on numeric data in Postgres:
SELECT LEAST(0, 127, 481, 620, -67);
The LEAST() function retrieves the smallest integer.
Example 4: How to Use the LEAST() Function With TEXT Data?
Let’s learn how the LEAST() function works on TEXT data:
SELECT LEAST('ALEX', 'Bob', 'John', 'Seth', 'Joseph');
The output snippet proves that the LEAST() function retrieves the smallest value concerning alphabetic order/sequence.
Example 5: How to Use the GREATEST() Function With DATES?
Run the following line of code to find the greatest date among the given dates:
SELECT GREATEST('2022-01-01', '2025-12-14', CURRENT_DATE);
The GREATEST() function retrieves the maximum date value from the given set of dates.
Example 6: How to Use the LEAST() Function With DATES?
Let’s learn how to find the least date from the given set of dates using the LEAST() function:
SELECT LEAST('2022-01-01', '2025-12-14', CURRENT_DATE);
The output proves the working of the LEAST() function, as it retrieves the smallest date among the given dates.
That’s it from this post!
Conclusion
In PostgreSQL, the GREATEST() and LEAST() are built-in functions used to get the biggest and smallest value from the given data, respectively. These functions are equally effective for analyzing/manipulating numeric and alphabetic data. In the case of textual data, these functions retrieve the data based on the alphabetic sequence/order. This blog post demonstrated the basic syntax, usage, and practical implementation of the GREATEST() and LEAST() functions in Postgres.
