Databases & Data

PostgreSQL Common Table Expressions

Using CTEs (WITH clause)

Basic CTE

WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 100000
)
SELECT * FROM high_earners; # define temporary result set

Multiple CTEs

WITH
    sales_summary AS (
        SELECT product_id, SUM(amount) AS total FROM sales GROUP BY product_id
    ),
    top_products AS (
        SELECT * FROM sales_summary WHERE total > 10000
    )
SELECT * FROM top_products; # chain CTEs

Recursive CTE

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates; # hierarchical query

Modifying CTE

WITH deleted AS (
    DELETE FROM old_orders WHERE date < '2020-01-01'
    RETURNING *
)
SELECT COUNT(*) FROM deleted; # DML in CTE