PostgreSQL - WITH Clause

PostgreSQL - The WITH Clause (Common Table Expressions)

In PostgreSQL, the WITH clause, also known as a Common Table Expression (CTE), allows you to define a temporary, named result set that exists only for the duration of a single query. CTEs are an incredibly powerful tool for breaking down large, complex queries into logical, readable, and maintainable steps.

Think of a CTE as creating a temporary view or table that you can reference multiple times within the main SELECT, INSERT, UPDATE, or DELETE statement. This is particularly useful for avoiding repetitive subqueries and for structuring recursive queries.

Syntax

The basic syntax for a WITH clause is as follows:

WITH cte_name (column1, column2, ...) AS (
    -- The query that defines the CTE
    SELECT ...
    FROM ...
)
-- The main query that uses the CTE
SELECT ...
FROM cte_name
WHERE ...;

You can also define multiple CTEs in a single WITH clause, separated by commas. Each subsequent CTE can reference the ones defined before it.

Simple CTE Example

Consider an employees table. Let’s find all employees who earn more than the average salary in the ‘Engineering’ department.

-- Sample employees table data:
-- id | name    | department    | salary
-- ---+---------+---------------+--------
-- 1  | Alice   | Engineering   | 90000
-- 2  | Bob     | Engineering   | 80000
-- 3  | Charlie | HR            | 60000
-- 4  | David   | Engineering   | 120000

WITH engineering_avg_salary AS (
    SELECT AVG(salary) as avg_sal
    FROM employees
    WHERE department = 'Engineering'
)
SELECT e.name, e.salary
FROM employees e, engineering_avg_salary eas
WHERE e.department = 'Engineering' AND e.salary > eas.avg_sal;

This query first calculates the average salary for the Engineering department in a CTE named engineering_avg_salary. The main query then joins the employees table with this CTE to filter for the desired employees. This is much cleaner than using a subquery in the WHERE clause.

Recursive CTEs

A powerful feature of CTEs is the ability to make them recursive using the WITH RECURSIVE syntax. This is ideal for querying hierarchical data, such as an organizational chart or a nested category structure.

Example: Find the management chain for a specific employee. Assume our employees table has id, name, and manager_id columns.

WITH RECURSIVE management_chain AS (
    -- Non-recursive part (the starting point)
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE name = 'Bob'

    UNION ALL

    -- Recursive part (joins with itself)
    SELECT e.id, e.name, e.manager_id, mc.level + 1
    FROM employees e
    JOIN management_chain mc ON e.id = mc.manager_id
)
SELECT name, level
FROM management_chain
ORDER BY level DESC;

This query starts with the employee ‘Bob’ (the non-recursive term) and then repeatedly joins the employees table to find each manager up the hierarchy until it reaches the top (where manager_id is NULL).

Data-Modifying Statements in CTEs

You can use INSERT, UPDATE, or DELETE inside a WITH clause. Combined with the RETURNING clause, this allows you to perform an action and immediately use the affected rows in another part of the query.

Example: Archive old orders by moving them from the orders table to an archived_orders table in a single statement.

WITH moved_rows AS (
    DELETE FROM orders
    WHERE order_date < '2023-01-01'
    RETURNING *
)
INSERT INTO archived_orders
SELECT * FROM moved_rows;

This atomically deletes old orders and inserts them into the archive table. The RETURNING * clause makes the deleted rows available to the moved_rows CTE, which is then used as the source for the INSERT statement.