PostgreSQL WHERE with examples

In this PostgreSQL tutorial, we will learn about the PostgreSQL WHERE clause. WHERE clause is used along with the SELECT statement to filter data according to a particular condition. By the end of this tutorial, you will have complete knowledge of the PostgreSQL WHERE clause.

• PostgreSQL WHERE
• PostgreSQL WHERE multiple values
• PostgreSQL WHERE multiple conditions
• PostgreSQL WHERE like
• PostgreSQL WHERE date is today
• PostgreSQL WHERE date greater than
• PostgreSQL WHERE in array
• PostgreSQL WHERE clause JSON

PostgreSQL WHERE

The PostgreSQL SELECT statement is used to retrieve data in form of rows from a table in a database. So basically we query this data from the table.

The basic syntax for SELECT statement is:

SELECT Column_List
FROM Table_Name;

So here, first of all, we specify a list of columns, i.e., a select list after the SELECT keyword. These are the columns that we require in the output. Then specify the name of the table after the FROM keyword from which you want to retrieve the data.

Since SELECT is a very complex statement, so there are many clauses that can be used to enhance the flexibility of the query to retrieve the data. Some of the clauses are listed below:

CLAUSE NAME	DESCRIPTION
DISTINCT	It is used to remove all the duplicate rows from the result set, i.e., it only selects the distinct rows from a table.
WHERE	It is used to filter the rows from a table according to a certain condition.
ORDER BY	It is used to sort the rows in a particular order such as ascending or descending order.
FETCH	It is used to fetch a particular subset from a set of rows returned by the query.
GROUP BY	It is used to group different rows into one single group.
HAVING	It is similar to the WHERE clause but it filters the data grouped by the GROUP BY clause.
FROM	It is mandatory and is used to specify the table name from which we need to retrieve the data. Here we can use two or more tables to retrieve data.

Now we will look at the practical implementation of SELECT statement.

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);

Now we will add some data in the table:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23');

Now we will use the SELECT statement to retrieve data from the table:

SELECT * FROM Employee;

In this section, we learned about the SELECT statement in PostgreSQL and we also learned about the clauses used with the SELECT statement to enhance its flexibility.

Now we will learn about the WHERE clause used with the SELECT statement in PostgreSQL in detail.

The syntax to use the WHERE clause is:

SELECT Column_List
FROM Table_Name
WHERE Condition;

The practical implementation for the WHERE clause is :

SELECT * 
FROM Employee
WHERE Dept='IT';

It is clear from the screenshot that we have filtered the data according to the Dept Column. We queried for the Department name as ‘ IT ‘ and in the query results we can see that we only get information of those employees whose department is ‘ IT ‘.

In this section, we learnt about the WHERE clause in PostgreSQL used with the SELECT statement.

PostgreSQL WHERE multiple values

In this section, we will learn about the WHERE clause in PostgreSQL and how can we retrieve data according to multiple values in a column.

One of the methods to select from multiple values is to use in clause along with the where clause. So the example for this method goes like this:

SELECT *
FROM Employee
WHERE Dept in('Electrical','Mechanical','HR');

It is clear from the screenshot that we want to retrieve only that data where department name is either ‘Electrical’, ‘Mechanical’ or ‘HR’. So we only receive the data having these three values as their Department names.

Another method to SELECT multiple values is that we can pass the whole list in form of an array. We will see the same example as shown above using this method.

SELECT *
FROM Employee
WHERE Dept = ANY ('{Electrical,Mechanical,HR}');

In this example we passed the list of departments in form of an array to retrieve the data from the table as per our requirement.

In this section, we learned about the WHERE clause in PostgreSQL and how can we retrieve data according to multiple values in a column.

Read: PostgreSQL WHERE IN

PostgreSQL WHERE multiple conditions

In this section, we will learn how to use the WHERE clause in PostgreSQL when data is to be filtered according to Multiple Conditions. There are two conjunctive operators namely AND and OR which are used to combine many conditions to retrieve data from a table.

First, we will learn about the AND operator used in PostgreSQL. The overall condition is considered true if and only if all the conditions are true while using AND Operator. The Syntax to use AND operator in SELECT statement in WHERE clause is:

SELECT Column_List
FROM Table_Name
WHERE Condition1 AND Condition2 AND Condition3;

So here we can specify as many conditions as we want and the data will be filtered accordingly.

The practical implementation of AND operator to incorporate multiple conditions to retrieve data in PostgreSQL are:

SELECT *
FROM Employee
WHERE Dept = 'IT' AND Salary > '2500';

It is clear from the screenshot that we want to filter data according to two conditions, one where Department is ‘ IT ‘ and also the salary should be greater than ‘ $ 3000 ‘. So we get the results according to this query in form of a table.

Now we will learn about the OR operator used in PostgreSQL. OR operator can be used to combine multiple conditions and it will fetch results for every true condition. The syntax to use OR operator along with WHERE clause in SELECT statement is:

SELECT Column_List
FROM Table_Name
WHERE Condition1 OR Condition2 OR Condition3;

Now we will see the practical implementation of OR operator in PostgreSQL:

SELECT *
FROM Employee
WHERE Dept = 'Mechanical' OR Salary > '2500';

It is clear from the screenshot that we want to filter data according to two conditions, one where Department is ‘ Mechanical ‘ or the salary should be greater than ‘ $ 2500 ‘. So we get the results according to this query in form of a table.

In this section, we learned how to use the WHERE clause when we want to filter according to Multiple Conditions in PostgreSQL. We used the AND and OR operator to combine multiple conditions to retrieve data from the table.

Read: How to create a table in PostgreSQL [Terminal + pgAdmin]

PostgreSQL WHERE like

In this section, we will learn about the LIKE operator used with the WHERE clause in PostgreSQL. LIKE is used to match string patterns using wildcards. It is used to ease the querying process. If the pattern matches then the LIKE operator returns TRUE.

• The two wildcard used for this process are:

WILCARD	DESCRIPTION
Percent Symbol (%)	A percent symbol represents that there can be any number of numbers or characters in its place
Underscore Symbol( _ )	An underscore symbol represents that there can be only one number or character in its place

The example for using these wildcards are as follows:

'sql' LIKE 'sql' -- TRUE
'sql' LIKE 's%'  -- TRUE
'sql' LIKE '_q_' -- TRUE
'sql' LIKE 's_'  -- FALSE

• To search for an exact match we can specify the string as it is. For eg: ‘sql’ LIKE ‘sql’
• To search for a uncertain string we will use wildcards, i.e., % and _. For eg: ‘sql’ LIKE ‘s%’

Now we will see the practical implementation of the LIKE operator using ‘ % ‘ wildcard:

SELECT * 
FROM Employee
WHERE Name LIKE 'J%';

It is clear from the screenshot that we have queried for names which start from the letter ‘J’ and we see the desired results as output.

Now we will see the practical implementation of the LIKE operator using ‘ % ‘ and ‘ _ ‘ wildcards both:

SELECT * 
FROM Employee
WHERE Name LIKE '_i%';

It is clear from the screenshot that we have queried for names that start from any character but should have ‘ i ‘ as the second character and we see the desired results as output.

In this section, we learned about the LIKE operator used with WHERE clause in PostgreSQL.

Read PostgreSQL INSERT INTO table

PostgreSQL WHERE date is today

In this section, we will learn about the WHERE clause in PostgreSQL and will learn about the NOW() function and how we can use it with the WHERE clause to retrieve data from a table in PostgreSQL.

NOW( ) function is used to return the current date and time in PostgreSQL. The code to retrieve the current date using the NOW() function is:

SELECT NOW()::DATE;

Now we will see an example from our table:

SELECT *
FROM Employee
WHERE Date_Of_Joining > now()::date-730;

In this example we have retrieved data such that we can see the data of those employees who have joined two years, i.e., 730 days ago and we get the desired results using the NOW function in PostgreSQL.

In this section, we learned about how we can use the NOW() Function in PostgreSQL to retrieve the current date and query data accordingly.

Read: How to connect to PostgreSQL database

PostgreSQL WHERE date greater than

In this section, we will learn about the Greater than Operator and how do we use it along with a DATE data type in PostgreSQL. Greater than operator can be used to compare values that are greater than a certain value given in an expression.

It can be used with the DATE data type to compare dates that are greater than a certain date in a given expression. The example to show the use of greater than operator with DATE data type is:

SELECT *
FROM Employee
WHERE Date_Of_Joining >= '2018-01-01';

In this example we have shown the details of those employees who have joined after ‘ 2018-01-01 ‘, so we used the greater than operator in order to compute the results.

In this section, we learned about the Greater than operator used with DATE data type in PostgreSQL.

PostgreSQL WHERE in array

In this section, we will learn about the WHERE clause used with arrays in PostgreSQL. Array is a data type in PostgreSQL that provides it with the functionality to store the same type of data in a sequential form. We can retrieve data from a table by putting some conditions on array data type as well.

For the practical implementation first create a table in PostgreSQL having one of the columns as Array data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE,
Phone integer[]);

Here we have created a table with one of the columns named as Phone which is of integer[ ] (Array) data type.

Now add data to this table using the INSERT statement:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25','{856741356,857412589}');

INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12','{745128963,745894172}');

INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21','{874125963,879654125}');

INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10','{985412477,985632147}');

INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16','{654781239,657412893}');

INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07','{745812396,745369812}');

INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13','{587412896,578941247}');

INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05','{874596321,879456142}');

INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24','{547812369,657412893}');

INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23','{978451236,941125367}');

The data has been successfully inserted in the table.

Now we will run a query on array data type using the WHERE clause:

SELECT* 
FROM Employee
WHERE Phone[1]=745128963;

Here we wanted to filter data according to the phone number of the employees which is of array data type. So we have applied the WHERE clause on a array data type to filter results according to our needs.

In this section, we learnt about the WHERE clause used with array data type in PostgreSQL.

Read: How to Uninstall PostgreSQL (Linux, Mac, and Windows)

PostgreSQL WHERE clause JSON

In this section, we will learn about the WHERE clause and how it is used with JSON data type in PostgreSQL. JSON stands for JavaScript Object Notation. It is made up of key-value pairs, so one needs to input data in form of key-value pairs.

In the example first create a table having a column of JSON data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE,
Address JSON);

Now we will add data into this table:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25','{"Lane":"6649 N Blue Gum St","City":"New Orleans"}');

INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12','{"Lane":"4 B Blue Ridge Blvd","City":"Brighton"}');

INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21','{"Lane":"8 W Cerritos Ave #54","City":"Bridgeport"}');

INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10','{"Lane":"639 Main St","City":"Anchorage"}');

INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16','{"Lane":"34 Center St","City":"Hamilton"}');

INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07','{"Lane":"3 Mcauley Dr","City":"Ashland"}');
 
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13','{"Lane":"7 Eads St","City":"Chicago"}');

INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05','{"Lane":"7 W Jackson Blvd","City":"San Jose"}');

INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24','{"Lane":"5 Boston Ave #88","City":"Sioux Falls"}');

INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23','{"Lane":"228 Runamuck Pl #2808","City":"Baltimore"}');

Now we will run a query using the WHERE clause on JSON data type:

SELECT * 
FROM Employee
WHERE Address ->> 'City'='Ashland';

In this example, we wanted to see the details of that employee whose address contains the city as ‘Ashland’. So we used the WHERE clause to get the results from the address column which is of JSON type.

You may like the following PostgreSQL tutorials:

• PostgreSQL CASE

In this tutorial, we learned, about WHERE clause in PostgreSQL. Also, we have covered these topics:

• PostgreSQL WHERE
• PostgreSQL WHERE multiple values
• PostgreSQL WHERE multiple conditions
• PostgreSQL WHERE like
• PostgreSQL WHERE date is today
• PostgreSQL WHERE date greater than
• PostgreSQL WHERE clause JSON
• PostgreSQL WHERE in array

For a better understanding of the topic watch my YouTube video: