PostgreSQL WHERE clause

By default, The SELECT statement retrieves all row in the data table. However, we may want to query for rows that meet a certain condition, such as students who is 18 years old.

In PostgreSQL, to return rows that satisfy a specified condition, use the WHERE clause in the SELECT statement.

In addition to the SELECT statement, you can use WHERE clauses in UPDATE and DELETE statements to specify rows to update or delete.

Usages of PostgreSQL WHERE Clauses

To find rows that satisfy a specified condition, use the PostgreSQL SELECT statement with the WHERE clause as follows:

SELECT columns_list
FROM table_name
WHERE query_condition;

To update rows that meet specified conditions, use the PostgreSQL UPDATA statement with the WHERE clause as follows:

UPDATA table_name
SET column_name = value1, ...
WHERE query_condition;

To delete rows that meet specified conditions, use the PostgreSQL DELETE statement with the WHERE clause as follows:

DELETE FROM table_name
WHERE query_condition;

PostgreSQL WHERE clause example

Here are some practical examples to demonstrate the power of WHERE clauses.

We will demonstrate these examples using the customer table from PostgreSQL Sakila sample database.

Example of using the WHERE clause with the equals (=) operator

The following statement uses the WHERE clause to find customers whose first name is JAMIE:

SELECT last_name,
  first_name
FROM customer
WHERE first_name = 'JAMIE';

 last_name | first_name
-----------+------------
 RICE      | JAMIE
 WAUGH     | JAMIE

use AND operator in WHERE clause

The following example finds customers with first name is JAMIE and last name is RICE using AND operator combining two Boolean expressions:

SELECT last_name,
  first_name
FROM customer
WHERE first_name = 'JAMIE'
  AND last_name = 'RICE';

 last_name | first_name
-----------+------------
 RICE      | JAMIE

use OR operator in WHERE clause

This example uses the OR operator to find customers whose last name is RODRIGUEZ or first name is ADAM:

SELECT first_name,
  last_name
FROM customer
WHERE last_name = 'RODRIGUEZ'
  OR first_name = 'ADAM';

 first_name | last_name
------------+-----------
 LAURA      | RODRIGUEZ
 ADAM       | GOOCH

use IN operator in WHERE clause

If you want to match a string to any string in a list, you can use the IN operator.

For example, the following statement returns customers named ANN,ANNE, or ANNIE:

SELECT first_name,
  last_name
FROM customer
WHERE first_name IN ('ANN', 'ANNE', 'ANNIE');

 first_name | last_name
------------+-----------
 ANN        | EVANS
 ANNE       | POWELL
 ANNIE      | RUSSELL

use LIKE operator in WHERE clause

To find strings that match a specified pattern, use the LIKE operator. The following example returns all customers whose name starting with ANN:

SELECT first_name,
  last_name
FROM customer
WHERE first_name LIKE 'ANN%';

 first_name | last_name
------------+-----------
 ANNA       | HILL
 ANN        | EVANS
 ANNE       | POWELL
 ANNIE      | RUSSELL
 ANNETTE    | OLSON

Here, wildcards % can match any character of any length. The pattern 'ANN%' matches any string starting with 'ANN'.

use BETWEEN operator in WHERE clause

The following example uses the BETWEEN operator to find customers whose names starting with A and contain 3 to 5 characters.

SELECT first_name,
  LENGTH(first_name) name_length
FROM customer
WHERE first_name LIKE 'A%'
  AND LENGTH(first_name) BETWEEN 3 AND 5
ORDER BY name_length;

 first_name | name_length
------------+-------------
 AMY        |           3
 ANN        |           3
 ANA        |           3
 ANDY       |           4
 ANNA       |           4
 ANNE       |           4
 ALMA       |           4
 ADAM       |           4
 ALAN       |           4
 ALEX       |           4
 ANGEL      |           5
 AGNES      |           5
 ANDRE      |           5
 AARON      |           5
 ALLAN      |           5
 ALLEN      |           5
 ALICE      |           5
 ALVIN      |           5
 ANITA      |           5
 AMBER      |           5
 APRIL      |           5
 ANNIE      |           5

In this example, we use the LENGTH() function.

Use the inequality operator (<>) operator in the WHERE clause

This example finds customers whose first name starting with BRA and whose last name is not MOTLEY:

SELECT first_name,
  last_name
FROM customer
WHERE first_name LIKE 'BRA%'
  AND last_name <> 'MOTLEY';

 first_name | last_name
------------+-----------
 BRANDY     | GRAVES
 BRANDON    | HUEY
 BRAD       | MCCURDY

You can use != operator instead of <> operator because they are equivalent.

Conclusion

This article introduced you to the usages of the WHERE clause and how to use the WHERE clause filter rows based on specified conditions.

Here are some operators that can be used in the WHERE clause:

• =: equal
• >: more than the
• <: less than
• >=: greater than or equal to
• <=: less than or equal to
• <>: not equal
• !=: not equal, equivalent to<>
• AND: logical AND operator
• OR: logical OR operator
• IN: returns true if the value matches any value in the list
• BETWEEN: returns true if a value is between a range of values
• LIKE: returns true if the value matches the pattern
• IS NULL: returns true if the value is NULL
• NOT: negates the result of other operators.