Published at Jul 19, 2024

Understanding WHERE in PostgreSQL (With Examples)

Written by Team Timescale

The WHERE clause in PostgreSQL (or TimescaleDB) filters data to retrieve only the relevant records. Using the WHERE clause allows you to specify conditions that must be met for the rows to be included in the query results.

What Is the WHERE Clause?

The WHERE clause in PostgreSQL is used to filter records based on a specified condition. It is one of the most commonly used clauses in SQL, enabling you to retrieve only those rows that meet the criteria defined in the condition.

PostgreSQL WHERE Syntax

The basic syntax of the WHERE clause is as follows:

SELECT column1, column2, ... FROM table WHERE condition;

Example table

Let's consider a table employees with the following structure and data:

CREATE TABLE employees ( id SERIAL PRIMARY KEY, department VARCHAR(50), salary NUMERIC, hire_date DATE );

INSERT INTO employees (name, department, salary, hire_date) VALUES ('Alice', 'Engineering', 70000, '2020-01-15'), ('Bob', 'HR', 50000, '2019-02-20'), ('Carol', 'Engineering', 75000, '2018-03-10'), ('Dave', 'Marketing', 60000, '2021-04-12'), ('Eve', 'Engineering', 72000, '2020-05-05'), ('Grace', 'Marketing', 58000, '2021-07-18'), ('Hank', 'Engineering', 69000, '2022-08-22'), ('Ivy', 'HR', 51000, '2019-09-30'), ('Jack', 'Marketing', 62000, '2020-10-25');

Practical examples with the WHERE clause

Let's dive into some examples to see the WHERE clause in action.

Example 1: Filtering by a single condition

Suppose we want to retrieve all employees from the Engineering department. We can use the WHERE clause to achieve this:

SELECT id, name, department, salary FROM employees WHERE department = 'Engineering';

Result:

id | name | department | salary ----+-------+-------------+--------| 1 | Alice | Engineering | 70000 3 | Carol | Engineering | 75000 5 | Eve | Engineering | 72000 8 | Hank | Engineering | 69000

This query returns all rows where the department column is Engineering.

Example 2: Using multiple conditions

Now, let's say we want to retrieve employees from the Engineering department who have a salary greater than $70,000. We can use the WHERE clause with multiple conditions:

SELECT id, name, department, salary FROM employees WHERE department = 'Engineering' AND salary > 70000;

Result:

This query filters the rows where the department is Engineering and the salary is greater than $70,000.

Example 3: Filtering by date

Let's retrieve employees who were hired after January 1, 2020. We can use the WHERE clause to filter based on the hire_date column:

SELECT id, name, department, hire_date FROM employees WHERE hire_date > '2020-01-01';

Result:

This query returns all employees who were hired after January 1, 2020.

Example 4: Using OR condition Suppose we want to retrieve all employees from either the HR or Marketing department. We can use the OR condition within the WHERE clause:

SELECT id, name, department, salary FROM employees WHERE department = 'HR' OR department = 'Marketing';

Result:

id | name | department | salary ----+-------+------------+-------- 2 | Bob | HR | 50000 4 | Dave | Marketing | 60000 6 | Frank | HR | 52000 7 | Grace | Marketing | 58000 9 | Ivy | HR | 51000 10 | Jack | Marketing | 62000

This query returns all employees who are in either the HR or Marketing department.

Conclusion

The WHERE clause is a fundamental part of SQL and PostgreSQL that allows you to filter query results based on specified conditions. By mastering the WHERE clause, you can write more precise and effective queries, ensuring that you retrieve only the data you need.

If you want to perform several aggregations in your query in PostgreSQL or TimescaleDB, you can add a WHERE clause to aggregate functions to extend the FILTER clause. Read our article on FILTER to learn more.