LEAST() and GREATEST() - Postgres SQL Amazing But Rarely Known Functions .

PostgresSQL is very powerful and one of the world's most advanced open-source relational databases. It gives much more flexibility and customization on query level when you want to perform a complex operation or complex queries on the database. Postgresql provides multiple operators and functions which make even complex operations and complex queries pretty easy and simple. Similarly LEAST() and GREATEST() are functions that make your work very easy and simple.

LEAST() and GREATEST() functions are very useful when you want to operate on different columns within the same row (a record) in a table. LEAST() and GREATEST() compare and find the minimum and maximum value respectively among given columns values in the same row. It's similar to finding a minimum or maximum value from an array of given values.

Here are a few simple examples you can try:
select LEAST(5, 3, 9, 2, 8, 1) and GREATEST(5, 3, 9, 2, 8, 1);

//should give 1 and 9 respectively

In-case of String or Varchar, it gives you results by sorting values alphanumerically:

select LEAST(ball, hockey, football, cricket) and GREATEST(ball, hockey, football, cricket);

//should give ball and hockey respectively.

There are few conditions MUST be satisfied when you are using LEAST() or GREATEST() functions in your query:

1. Columns on which you want to apply function MUST be of same data type OR values MUST of the same type i.e. numeric or string or decimal etc.

2. If there is any column which is having different types of data types among others, you can use casting to convert values to the common data type. like: CAST(column name as datatype)

Use:

There are many use-cases of using LEAST() and GREATEST() in your query when you have to filter records comparing values within a row itself.

 
Consider a case, when you have a car subscription system in which a car can have multiple prices offers base on the subscription term or period. But when you list your cars on the landing page and you have to show the minimum price only among all given offer prices for a car. Given all prices, variation is saved in the same row with other car details.

You can have a query to do that :

select car_name, LEAST(month_price, six_month_price, nine_month_price, one_year_price) from cars;

//given all price having per month value.

In another case, consider database table student_marks in which each subject represented by column i.e. maths, science, history, english, hindi, student_id (foriegn_key).

Now when you have to find student list with their least and maximum marks in any subject, you can write your query like:

select student_id, LEAST(maths, science, history, english, hindi) as least_marks, GREATEST(maths, science, history, english, hindi) as maximum_marks from student_marks;

Similarly can find student having merit in all subject.

select student_id from student_marks where LEAST(maths, science, history, english, hindi) > 60;

I hope when you come across any similar situation like this, these functions LEAST() and GREATEST() will help you a lot to keep code clean and simple.

Feel free to leave your comments as feedback or in case you have any queries.