Common Table Expressions (CTEs) in PostgreSQL

Common Table Expressions (CTEs) are a powerful feature in PostgreSQL that enable you to create temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They can improve the readability and organization of complex queries.

A Common Table Expression (CTE) is defined using the WITH clause, followed by a query that generates a result set. This result set can then be used as a temporary table in subsequent queries.

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table
    WHERE condition
)
SELECT * FROM cte_name;

In the above syntax:

• cte_name: The name of the CTE.
• SELECT ...: The query that defines the CTE.
• SELECT * FROM cte_name: Query that references the CTE.

Example 1: Simple CTE

WITH department_totals AS (
    SELECT department_id, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id
)
SELECT * FROM department_totals;

Example 2: CTE with Recursive Query

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

• Use descriptive names for CTEs to enhance readability.
• Limit the use of CTEs for performance-intensive queries; consider alternatives like temporary tables for large datasets.
• Keep CTE definitions simple; complex queries may reduce maintainability.