PostgreSQL WHERE: Filtering Rows

Summary: in this tutorial, you will learn how to use the PostgreSQL WHERE clause to filter rows in a table.

PostgreSQL WHERE clause

The SELECT FROM statement retrieves data from one or more columns of all rows of a table. In practice, you often need to select rows that meet a condition.

To filter rows from a table based on a condition, you use the WHERE clause in the SELECT statement:

SELECT column1, column2, ...
FROM table_name
WHERE condition;Code language: SQL (Structured Query Language) (sql)

When executing the statement, PostgreSQL evaluates the FROM clause first, then the WHERE clause, and finally the SELECT clause:

• First, retrieve each row in the table_name by running the FROM clause.
• Second, evaluate the condition in the WHERE clause and include the row in the result set if the condition is true.
• Third, select the columns specified in the SELECT clause from the selected rows.

If no rows from the table_name meet the condition, the SELECT statement returns an empty set, which contains no rows.

PostgreSQL WHERE clause example

Suppose we have the inventories table as follows:

CREATE TABLE inventories (
  name VARCHAR(255),
  brand VARCHAR(50),
  quantity INT,
  price DECIMAL(19, 2)
);


INSERT INTO inventories (name, brand, quantity, price)
VALUES ('iPhone 14 Pro', 'Apple', 10, 999.99),
       ('Galaxy S23 Ultra', 'Samsung', 15, 1199.99),
       ('Pixel 7 Pro', 'Google', 8, 899.99),
       ('Xperia 1 IV', 'Sony', 7, 1299.99);Code language: SQL (Structured Query Language) (sql)

The following statement uses the WHERE clause to retrieve the products with a quantity greater than 9:

SELECT name, quantity
FROM inventories
WHERE quantity > 9;Code language: SQL (Structured Query Language) (sql)

Output:

       name       | quantity
------------------+----------
 iPhone 14 Pro    |       10
 Galaxy S23 Ultra |       15Code language: SQL (Structured Query Language) (sql)

How it works.

First, the FROM clause selects all rows from the inventories table:

       name       |  brand  | quantity |  price
------------------+---------+----------+---------
 iPhone 14 Pro    | Apple   |       10 |  999.99
 Galaxy S23 Ultra | Samsung |       15 | 1199.99
 Pixel 7 Pro      | Google  |        8 |  899.99
 Xperia 1 IV      | Sony    |        7 | 1299.99Code language: SQL (Structured Query Language) (sql)

Second, the WHERE clause evaluates each row in the inventories table, checks if the quantity is greater than 10, and includes the rows in the intermediate result set:

       name       |  brand  | quantity |  price
------------------+---------+----------+---------
 iPhone 14 Pro    | Apple   |       10 |  999.99
 Galaxy S23 Ultra | Samsung |       15 | 1199.99Code language: SQL (Structured Query Language) (sql)

It returns two rows with quantity 10 and 15.

Third, the SELECT clause retrieves data from the name and quantity columns:

       name       | quantity
------------------+----------
 iPhone 14 Pro    |       10
 Galaxy S23 Ultra |       15Code language: SQL (Structured Query Language) (sql)

Comparison operators

Besides the greater than operator >, you can utilize other comparison operators inside the WHERE clause:

Equal operator (=)

The equal operator returns true if two values are equal.

For example, the following statement uses the equal to operator (=) in the WHERE clause to query the products from the inventories with a quantity equal to 10:

SELECT name, quantity
FROM inventories
WHERE quantity = 10;Code language: SQL (Structured Query Language) (sql)

Output:

     name      | quantity
---------------+----------
 iPhone 14 Pro |       10Code language: SQL (Structured Query Language) (sql)

The query returns the iPhone 14 Pro because only this product has a quantity of 10.

Not equal operator ( !=)

The following statement uses the not equal operator (!=) to retrieve products from the inventories table with the quantity not equal to 10:

SELECT name, quantity
FROM inventories
WHERE quantity != 10;Code language: SQL (Structured Query Language) (sql)

Output:

       name       | quantity
------------------+----------
 Galaxy S23 Ultra |       15
 Pixel 7 Pro      |        8
 Xperia 1 IV      |        7Code language: SQL (Structured Query Language) (sql)

Note that PostgreSQL also uses <> as the not equal to operator, so you can use the != and <> operators interchangeably.

The WHERE clause and column alias

Since PostgreSQL evaluates the WHERE clause before the SELECT clause, the column aliases are not available in the WHERE clause.

The following statement attempts to use the amount column alias in the WHERE and results in an error:

SELECT
  name,
  quantity * price AS amount
FROM
  inventories
WHERE
  amount > 10000;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Error:

column "amount" does not existCode language: plaintext (plaintext)

The output indicates that the amount column does not exist. To fix this error, you need to use the full expression in the WHERE clause as follows:

SELECT
  name,
  quantity * price AS amount
FROM
  inventories
WHERE
  quantity * price > 10000;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

       name       |  amount
------------------+----------
 Galaxy S23 Ultra | 17999.85Code language: plaintext (plaintext)

Summary

• Use the WHERE clause to filter rows from a table based on a condition.
• PostgreSQL evaluate the WHERE clause after the FROM clause and before the SELECT clause.