PostgreSQL Common Table Expressions (CTEs)

A Common Table Expression (CTE) in PostgreSQL is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve query readability, simplify complex queries, and allow recursive operations.

1. Basic CTE Syntax

WITH cte_name AS (
                -- SQL query
                SELECT column1, column2
                FROM table_name
                WHERE condition
            )
            SELECT *
            FROM cte_name;

2. Simple Example

-- Get employees with salary above average using CTE
            WITH high_salary_employees AS (
                SELECT id, name, salary
                FROM employees
                WHERE salary > (SELECT AVG(salary) FROM employees)
            )
            SELECT *
            FROM high_salary_employees
            ORDER BY salary DESC;

3. Multiple CTEs

You can define multiple CTEs by separating them with commas.

WITH dept_salary AS (
                SELECT department_id, SUM(salary) AS total_salary
                FROM employees
                GROUP BY department_id
            ), high_salary_dept AS (
                SELECT department_id
                FROM dept_salary
                WHERE total_salary > 500000
            )
            SELECT e.id, e.name, e.salary, e.department_id
            FROM employees e
            JOIN high_salary_dept hsd ON e.department_id = hsd.department_id;

4. Recursive CTEs

Recursive CTEs allow queries to refer to their own result, useful for hierarchical or tree-structured data.

-- Example: Employee hierarchy
            WITH RECURSIVE employee_hierarchy AS (
                SELECT id, name, manager_id, 1 AS level
                FROM employees
                WHERE manager_id IS NULL -- Top-level managers

                UNION ALL

                SELECT e.id, e.name, e.manager_id, eh.level + 1
                FROM employees e
                INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
            )
            SELECT *
            FROM employee_hierarchy
            ORDER BY level, manager_id;

5. Advantages of CTEs

• Improves query readability and organization.
• Can be referenced multiple times in a query without repetition.
• Supports recursive queries for hierarchical data.
• Can simplify complex joins, aggregations, and subqueries.

6. Best Practices

• Use descriptive names for CTEs for clarity.
• Keep CTEs concise; avoid unnecessary large intermediate datasets.
• Use recursive CTEs only when needed to prevent performance issues.
• Combine multiple CTEs carefully and ensure correct order of execution.

Conclusion

Common Table Expressions (CTEs) are powerful tools in PostgreSQL to simplify complex queries, create reusable result sets, and handle recursive hierarchies. By using CTEs effectively, you can write more maintainable, readable, and efficient SQL queries. In the next tutorial, we will explore PostgreSQL JSON and JSONB, which allow flexible storage and querying of JSON data.