PostgreSQL WHERE Clause

In PostgreSQL, the WHERE condition is used to define the filtering requirements for obtaining data from one or more tables. It enables you to only retrieve the rows that satisfy the query’s criteria. In this PostgreSQL tutorial, we will learn PostgreSQL WHERE Condition and its syntax with practical examples to show its usage.

PostgreSQL WHERE Clause

The WHERE clause in PostgreSQL serves as a conditional filter, allowing you to extract only records that fulfill a specific condition.

Although it can also be used with other SQL statements like UPDATE and DELETE, the WHERE clause is primarily used with the SELECT statement.

Syntax

The following is the syntax of the WHERE Condition.

SELECT column1, column2, Column3 ...
FROM table_name
WHERE condition;

Here, table_name is the name of the table you want to query, and column1, column2, column3,…, represent the columns you want to get and condition is a representation of the filtering criteria.

Common operators for the WHERE condition include:

• Comparison Operators: “=”, “<>”, “>”, “<“, “>=”, “<=”
• Logical Operators: “AND”, “OR”, “NOT”
• Pattern Matching Operators: “LIKE”, “ILIKE”
• Range Operators: “BETWEEN”, “NOT BETWEEN”
• Membership Operators: “IN”, “NOT IN”
• NULL Operators: “IS NULL”, “IS NOT NULL”

Let’s see some examples to show the usage of the WHERE condition in PostgreSQL.

Example-1 Getting Specific Rows

Here, I have a table called “employees”, which has the columns “employee_id”, “first_name”, “last_name”, and “salary”.

Now, we will see how to use the query below to find employees with a salary of more than $50,000.

SELECT employee_id, first_name, last_name
FROM employees
WHERE salary > 50000;

The above query gets the employee_id, first_name, and last_name of the employees whose salary is greater than $50,000. The WHERE condition defines the filtering criteria by comparing the value 50000 with the “salary” column using the “>” (greater than) operator.

After executing the above query, we got the expected output, as shown in the below screenshot.

There are four employees, William, Olivia, James, and Benjamin, each with a salary over $50000.

Example-2 Using Multiple Conditions with PostgreSQL where clause with AND operator

To get the employees with a salary greater than $50,000 and who were hired after January 1, 2023, use the query below.

SELECT employee_id, first_name, last_name, salary, hire_date
FROM employees
WHERE salary > 50000 AND hire_date > '2023-01-01';

The above query gets the employee_id, first_name, and last_name, salary, and hire_date of the employees who meet two specific conditions. WHERE salary > 50000 AND hire_date > ‘2023-01-01’, This line sets the data filtering conditions.

After executing the above query, I got the expected output, as shown in the below screenshot.

Example-3 Using Pattern Matching with LIKE operator and wildcard(“%”)

To find the employees whose last names begin with “Smi” or any other letters, use the LIKE operator with a wildcard(“%”) in the WHERE condition.

SELECT employee_id, first_name, last_name
FROM employees
WHERE last_name LIKE 'Smi%';

The above query gets the employee’s employee_id, first_name, and last_name whose last_name starts with “Smi”. The WHERE condition uses the “LIKE” operator along with a pattern.

The pattern “Smi%” specifies that the last name should start with “Smi” followed by any number of characters. The ‘%’ wildcard represents any number of characters. So, one employee, Olivia, has the last name Smith, which starts with “Smi.”

After executing the above query, I got the expected output, as shown in the below screenshot.

Example-4 Using the OR Operator

The query below is to find appliances priced less than 10000 or have an inventory count greater than four nos.

SELECT product_id, product_name, price, category, inventory_count
FROM appliances
WHERE price < 10000
OR inventory_count > 4;

After executing the above query, I got the expected output, as shown in the below screenshot.

Example-5 Using NOT Operator

The query below will list the number of orders whose statuses have not been canceled.

SELECT order_id, customer_name, status, order_date 
FROM ProductOrders 
WHERE NOT status = 'canceled';

After executing the above query, I got the expected output, as shown in the below screenshot.

You can also specify the same query in the format below.

SELECT order_id, customer_name, status, order_date 
FROM ProductOrders 
WHERE status != 'canceled';

After executing the above query, I got the same expected output as shown below.

Example-6 Using the BETWEEN Operator

The below query will get you the lists of appliances in those price ranges between 1000 and 5000.

SELECT product_id, product_name, price, category, inventory_count
FROM appliances
WHERE price BETWEEN 1000 AND 5000;

After executing the above query, I got the expected output, as shown in the below screenshot.

Or, for the same output you can also use the query below.

SELECT product_id, product_name, price, category, inventory_count
FROM appliances
WHERE price >= 1000 AND price <= 5000;

Example-7 Using IN Operator

The IN operator allows you to specify multiple values in a WHERE clause. We can use the query below to get the employees’ lists belonging to the HR and IT departments.

SELECT *
FROM employees
WHERE department IN ('HR', 'IT');

After executing the above query, I got the expected output as shown in the below screenshot.

Example-8 Using Null Values

Use the IS NULL operator in the WHERE condition to get the employees with a null value in the “department_id” column.

The below query gets the employee’s employee_id, first_name, and last_name of employees whose “department_id” column has a null value. The WHERE condition uses the “IS NULL” operator to check if the “department_id” is null.

SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id IS NULL;

After executing the above query, I got the expected output shown in the screenshot below.

So there is one employee, Emma Brown, who doesn’t belong to any department.

Conclusion

Knowing the PostgreSQL WHERE clause is essential for database querying and optimization. In this PostgreSQL tutorial, we learned about the PostgreSQL WHERE condition, with different examples like using specific rows, adding multiple conditions, pattern matching, and null values in the WHERE condition.

You may like to read:

• How to Create View in PostgreSQL
• ILIKE Case Insensitive in PostgreSQL
• PostgreSQL Add Primary Key Autoincrement