PostgreSQL tables can store a huge amount of data. But we are usually interested in some particular kind of data, not the whole data records consisting of thousands of entries. To get some particular kind of data that is of interest in any way, we need to filter it for that value/interest. This is possible by utilizing the WHERE clause.
The WHERE clause filters the data for the condition we specify to it and returns the data that satisfies the specified condition. Let’s see how the WHERE clause can be used to filter any table’s data.
WHERE Clause PostgreSQL – How to Filter Table’s Data?
The WHERE clause in PostgreSQL is used to filter some specific data from a table. The basic syntax for a WHERE clause is
SELECT * FROM t_name
WHERE cond;
Syntax Description
- The SELECT * will return “all” the records that will satisfy the condition.
- The table name is entered after the FROM keyword.
- After the WHERE statement, the condition is defined. The data record that fulfills the condition is returned by the query.
Let’s see different use cases for the WHERE clause.
Example 1: Filter Data Using WHERE Clause
Let’s consider the table named “messages”.
Now we will filter the data of the selected table “messages”. Let’s say we want to filter those messages that were Delivered but still not read. In this regard, we will write the following query:
SELECT * FROM messages
WHERE msg_status = 'Delivered';
This will return those records where the message_status is “Delivered” like this:
We can see that only those records are returned where the status was “Delivered”. The WHERE clause can also be used with AND/OR operators to filter the data based on multiple conditions. Let’s use it with the AND operator.
Example 2: Using WHERE Clause With AND Operator
We use the WHERE clause with the AND operator to filter the data that satisfies all the specified conditions. Let’s suppose we want to filter the records where the message status is “Delivered” and the message is sent to “Marie”. For this, we will write the following query:
SELECT * FROM messages
WHERE msg_status = 'Delivered' AND
sent_to = 'Marie';
The query will return all those records where both the conditions are satisfied i.e. the message status is “Delivered” and it is sent to “Marie”.
We can observe that the query has returned only that record where both the conditions were satisfied.
There are many other operators and statements that can complement the WHERE clause to perform custom tasks. We will be using them as we move towards advanced concepts of PostgreSQL.
Conclusion
In PostgreSQL, the WHERE statement gets the filtered data from a wide range of data. The WHERE clause when used with the SELECT statement returns the data records which satisfy the condition given after the WHERE clause. In this way, we can get the data we are interested in rather than searching for this data in the whole huge record of data entries. This tutorial has taught us about the WHERE clause with the help of examples and proper description.
