Common Table Expression (CTE) in PostgreSQL

PostgreSQL: Common Table Expression (CTE)

A Common Table Expression (CTE) in PostgreSQL, also known as a "WITH" query, allows you to create temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are particularly useful for breaking down complex queries into simpler parts, improving readability, and making maintenance easier.

Syntax:

The basic syntax of a CTE in PostgreSQL is:

WITH cte_name AS (
    -- inner query
)
SELECT * FROM cte_name;

Here:

• WITH starts the CTE.
• cte_name is the name of the CTE that you can refer to in the main query.
• The inner query inside the parentheses is the actual query that defines the CTE.

You can also chain multiple CTEs by separating them with commas:

WITH cte1 AS (
    -- first query
), 
cte2 AS (
    -- second query
)
SELECT * FROM cte1
JOIN cte2 ON cte1.id = cte2.id;

Example 1: Basic CTE

Code:

-- Define a CTE that calculates the total sales for each customer
WITH total_sales AS (
    SELECT customer_id, SUM(sales_amount) AS total_amount
    FROM sales
    GROUP BY customer_id
)
-- Use the CTE in the main query to retrieve customers with high sales
SELECT customer_id, total_amount
FROM total_sales
WHERE total_amount > 5000;

Explanation:

• This CTE, named total_sales, calculates the total sales for each customer by summing sales_amount for each customer_id. The main query then selects from total_sales to find customers with sales greater than 5,000.

Example 2: Recursive CTE

Recursive CTEs are useful for hierarchical or tree-structured data, such as finding all employees under a manager.

Code:

-- Define a recursive CTE to find all employees under a specific manager
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor member: starting point of recursion
    SELECT employee_id, manager_id, employee_name
    FROM employees
    WHERE manager_id = 1  -- Start with the top-level manager

    UNION ALL

    -- Recursive member: gets subordinates for each employee in the previous step
    SELECT e.employee_id, e.manager_id, e.employee_name
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- Use the recursive CTE to get all employees in the hierarchy
SELECT * FROM employee_hierarchy;

Explanation:

• This recursive CTE, employee_hierarchy, finds all employees in the reporting chain under a specified manager (manager_id = 1). The anchor member defines the top-level manager, while the recursive member finds subordinates at each level.

Important Notes:

1. Performance:

• CTEs are evaluated once, unlike subqueries in the main query that may be re-evaluated multiple times. This can improve performance in certain cases.

2. Recursive CTEs:

• Recursive CTEs are particularly powerful for querying hierarchical data (e.g., organizational charts). They work by iterating until no more records meet the criteria, ensuring a full depth traversal.

3. Readability and Maintenance:

• CTEs can simplify complex queries by breaking them into logical parts. This makes the code easier to read, understand, and maintain.

Summary:

Common Table Expressions (CTEs) are versatile and powerful tools in PostgreSQL for simplifying complex queries, especially with recursive structures. Using CTEs can enhance the readability and maintainability of SQL code, as well as optimize certain types of queries.

All PostgreSQL Questions, Answers, and Code Snippets Collection.