In this PostgreSQL tutorial, we will learn about Postgresql having clauses and cover the following topics.
- Postgresql having clause
- Postgresql having count greater than
- Postgresql having max
- Postgresql having min
- Postgresql having without group by
- Postgresql having function
- Postgresql having vs where
- Postgresql having subquery
- Postgresql having distinct
Postgresql having clause
In Postgresql, the Having clause is used as a search condition for an aggregate function or a group. To filter the groups or aggregate based on a given or stated criteria, we’ll utilize the HAVING clause with the GROUP BY clause.
The syntax of the HAVING clause is given below.
SELECT
col_1,
aggregate_func (col_2)
FROM
table_name
GROUP BY
col_1
HAVING
condition;
In the above syntax, Group by clause returns rows grouped by col_1, the Having clause will filter, this groups based on a specified condition.
Also, read: Postgresql while loop
Postgresql having count greater than
In Postgresql, with the help of the HAVING clause, we can filter the result returned by the count function which is greater than some specified value.
The table we will use in this tutorial is employe and the description of the table is given below.

Now, will count the first name of the employees using the count function.
SELECT first_name, count(first_name) FROM employee
GROUP BY first_name;
In the above code, we are counting the first_name of the employee using the aggregate function named COUNT(), and grouping it by first_name.
The output of the above code is given below.

In the above output, we can see that Divine is the first_name of 230 employees and so on.
Let’s filter the employees whose first_name is greater than 240 using the having clause.
SELECT first_name, count(first_name) FROM employee
GROUP BY first_name
HAVING count(emp_no) > 240;
In the above code, we are filtering the result using the having clause, which will show the first_name of employees having first_name greater than 240.

In the above output, it shows the employee’s first_name greater than 240 like Masaru has 242.
Also, check: Postgresql REGEXP_REPLACE Function
Postgresql having max
In Postgresql, If we use the MAX() function in a HAVING clause, we can apply a filter for a group.
Let’s run the following query that selects only the highest salary paid to each employee and the salaries are greater than 28000.
SELECT name, max(salary_$) FROM emp_info
GROUP BY age, name
HAVING max(salary_$) > 28000;
The output of the above code is given below.

In the above output, Rony, Lillian, Dan has salaries greater than 28000.
Let’s run the max function with having on another table, here we have a table named major_cities that contains the city name of the United Kingdom and the population of that city.

Now, we will filter the city which has the maximum population.
SELECT city_name, max(population) FROM major_cities
GROUP BY city_name
HAVING max(population) > 580000;
The code will filter the city name whose population is greater than 580000.

There are five cities in the United Kindom whose population is greater than 580000 and the city name is Briston, Birmingham, London, Glasgow, Liverpool.
Read: Postgresql escape single quote
Postgresql having min
In Postgresql, If we use the MIN() function in a HAVING clause, we can apply a filter for a group. The MIN function returns the minimum value from the column or list of values.
Let’s run the following query that selects only the Lowest salary paid to each employee and the salaries are less than 45000.
SELECT name, min(salary_$) FROM emp_info
GROUP BY age, name
HAVING max(salary_$) < 28000;
The output of the above code is given below.

In the above code, Gregory, Iris, Robert has minimum a salary of less than 28000.
Read: Postgresql function return table
Postgresql having without group by
In Postgresql, we can use the having clause without group by clause, let’s run the below simple example.
select (2*2) having 2 =2;
In the above code, it will return the select statement result, if having condition is true, otherwise returns nothing.
Remember, the HAVING clause is executed after FROM, WHERE, GROUP BY, and before SELECT, DISTINCT, ORDER BY, LIMIT clause.
The output of the above code is given below.

In the above output, the result returned by the SELECT statement is 4, because the HAVING condition becomes true.
Read: PostgreSQL Date Difference
Postgresql having vs where
In Postgresql, there is a difference between having and where clause.
Where clause is used to filter the row while having clause for a group of rows.
Let’s understand through an example.
SELECT name FROM emp_info
WHERE salary_$ >30000;
In the above code, we are filtering the name of employees based on the condition salary_$ > 30000 using the WHERE clause. The condition is applied on each row one by one.
The output of the above code is given below.

Now, we will get the same result as the above one using the HAVING clause.
SELECT name,max(salary_$) FROM emp_info
GROUP BY salary_$,name
HAVING max(salary_$) >30000;
The output of the above code is given below.

In the above output, the result is the same as we got using the WHERE clause.
Read: PostgreSQL WHERE with examples
Postgresql having subquery
In Postgresql, we can use the having clause with subquery, a subquery is a query within a query.
Let’s run the below query.
SELECT name,max(salary_$) FROM emp_info
GROUP BY salary_$,name
HAVING max(salary_$) > (SELECT salary_$ FROM emp_info
WHERE salary_$ = 45000);
The output of the above code is given below.

Postgresql having distinct
In Postgresql, we can use the distinct function with the Having clause, the distinct function returns the unique values from a column.
The table that we will use here is data_info and the description of the table is given below.

Let’s run the below query that has a distinct function with the HAVING clause.
Select Type, Color, Count(Distinct Location) As UniqueLocations
From data_info
Group By Type, Color
Having Count(Distinct Location) > 1
The output of the above code is given below.

Also, take a look at some more PostgreSQL tutorials.
- PostgreSQL WHERE IN with examples
- How to migrate from MySQL to Postgres
- Postgresql current_timestamp
- Postgresql Format
- Postgresql Add Foreign Key
- Postgresql listen_addresses
- Postgresql import SQL file
So, in this tutorial, we have learned about Postgresql having a clause. And we have also covered the following topics.
- Postgresql having clause
- Postgresql having count greater than
- Postgresql having max
- Postgresql having min
- Postgresql having without group by
- Postgresql having function
- Postgresql having vs where
- Postgresql having subquery
- Postgresql having distinct
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.