Postgres With

CTE or Common Table Expression is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times.

In this tutorial, we will explore how to create the Common Table Expressions in PostgreSQL using the PostgreSQL WITH keyword.

PostgreSQL WITH Clause

The following defines the syntax of a PostgreSQL CTE using the WITH keyword:

WITH cte_name (column_list) AS (
CTE_query_definition
)
statement;

We start by specifying the name of the CTE, followed by an optional column list.

The next step is the CTE body. In this section, we define the query that returns a result. If we do not specify the column list after the CTE name, the select list of the query_definition becomes the column list of the CTE.

Finally, we use the CTE like a table or a view using any CRUD command.

PostgreSQL CTE Example

Consider the following table and sample data that we use to demonstrate the use of CTEs in PostgreSQL:

-- Create the sample table
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(100),
salary NUMERIC(10, 2)
);

-- Insert sample data
INSERT INTO employees (name, department, salary)
VALUES ('J Doe', 'Finance', 5000.00),
('Jane Smith', 'Finance', 5500.00),
('Mark Johnson', 'Sales', 4500.00),
('Emily Brown', 'Sales', 4800.00),
('Michael Davis', 'IT', 6000.00),
('Sarah Wilson', 'IT', 6500.00);

Once the table is set up, we can learn how to use CTEs in PostgreSQL.

Example 1: Simple CTE

The following example demonstrates how we can perform a simple CTE in PostgreSQL using the employees’ table that we created earlier:

WITH finance_employees AS (
SELECT *
FROM employees
WHERE department = 'Finance'
)
SELECT *
FROM finance_employees;

Resulting Output:

You can check the query analysis as follows:

Example 2: CTE with Multiple Steps

The following shows how to perform a CTE with multiple steps:

WITH department_totals AS (
SELECT department, SUM(salary) AS total_salary, COUNT(*) AS num_employees
FROM employees
GROUP BY department
),
department_averages AS (
SELECT department, total_salary / num_employees AS average_salary
FROM department_totals
)
SELECT *
FROM department_averages;

Output:

The example returns the average salary by department using the multi-step CTE.

Conclusion

This short tutorial explored how to build and work with CTEs in PostgreSQL. To learn more, you can check our tutorial on PostgreSQL recursive CTEs.