PostgreSQL Min With Examples

This PostgreSQL tutorial explains everything about PostgreSQL Min function with a few examples. Here we will check:

• Postgresql min of two values
• Postgresql min date
• Postgresql min function
• Postgresql min with condition
• Postgresql min timestamp
• Postgresql min over partition
• Postgresql array min

Postgresql min function

The Postgresql MIN function is used to find the minimum value, we can find the minimum value in a column or list that contains many values. The MIN function is also called aggregate function in Postgresql.

The syntax of the MIN function is given below.

SELECT 
    MIN(expression) 
FROM 
   table_name
...;

In the above code, the column name is supplied to the MIN() function of a table to find the minimum value in a column. The data type of the column can be any comparable type like a number, string, etc.

The table we will use here is emp_info and the description of the table is given below.

The above table contains five columns named id, name, age, address, salary, where the address contains country and city name like United Kindom (California, Boston, New York) and the country Brazil.

In the above table, there is a column named salary_$, let’s find the minimum salary.

SELECT min(salary_$) FROM emp_info;

In the above code, min(salary_$) will return the minimum salary from column salary_$ in table emp_info.

The output of the above code is given below.

In the above output, the minimum salary is 20000.

Also, check: PostgreSQL Update + Examples

Postgresql min of two values

In Postgresql, we can find the minimum of two values using the LEAST function. Because the MIN function works on a group of data or it accepts only column values. So we can’t provide two values directly to it.

The syntax of the LEAST function is given below.

LEAST(data_1,data_2)

The code will return the minimum of data1 and data_2.

Let’s find the minimum between 2 and 3.

SELECT least(2,3);

The output of the above code is given below.

As we can see in the above output, the minimum of two values 2 and 3 is 2.

Read: Postgresql date comparison

Postgresql min with condition

In Postgresql, the MIN function can be used with some conditions.

Run the below query to find the minimum salary which is greater than 28000.

SELECT min(salary_$) FROM emp_info
WHERE salary_$ > 28000;

In the above code, we are fetching the minimum salary using the min(salary_$), and filtering with some conditions using the (WHERE salary_$ > 28000).

The output of the above code is given below.

In the above output, the minimum salary greater than 28000 is 45000.

Read: Postgresql date to string

Postgresql min date

In Postgresql, the MIN function can also return the minimum date from a column containing dates.

The table we will use here is a journey and the description of the table is given below.

As we can see in the above table journey has two columns that contain the date and time named arrival, departure.

Let’s find the minimum arrival time.

SELECT min(arrival)::date FROM journey;

In the above code, we have passed the arrival column to the min function, and this will return the minimum datetime. ‘min(arrival)::date‘ where ::date means we are casting the result from min function to date because we need the only date, not a time.

The output of the above code is given below.

In the above output, the minimum date is 2017-05-06 in a column named arrival.

Read: Postgresql escape single quote

Postgresql min timestamp

In Postgresql, the min function can be used to find the minimum timestamp in a column.

We are going to use the same table journey that was used in the above sub-sections “Postgresql min date”.

Let’s find the minimum departure timestamp in a column named departure of a table journey.

SELECT min(departure) FROM journey;

In the above code, we have passed the column named departure to min function. This will return the minimum timestamp from a column department.

The output of the above code is given.

In the above output, the minimum departure time is 2017-02-24 12:00:00.

Read: PostgreSQL list users

Postgresql min over partition

In Postgresql, we can find the minimum column values using the WINDOW function that is OVER. The OVER clause decides exactly how the rows of the query are split up for the processing by the window function.

The table we will use in this section is emp_info that contains the information about the employees like name, age, salary, address. The description of the table is given below.

Let’s run the below code.

SELECT address, name, age, min(salary_$) 
OVER (PARTITION BY address) FROM emp_info;

The output of the above code is given below.

You may also like to read the following PostgreSQL tutorials.

• Postgresql function return table
• PostgreSQL Date Difference
• PostgreSQL Update Join + Examples
• PostgreSQL list databases
• Postgresql Delete Row
• Postgresql Joins – How to use
• Postgresql date between two dates
• Postgresql ilike case insensitive

So, in this tutorial, we have learned about “Postgresql min” and covered the following topics.

• Postgresql min of two values
• Postgresql min date
• Postgresql min function
• Postgresql min with condition
• Postgresql min timestamp
• Postgresql min over partition
• Postgresql array min