PostgreSQL CTE (Common Table Expressions)

Key CTE Concepts

WITH cte AS (SELECT ...)

WITH RECURSIVE cte AS ...

WITH cte1 AS (...), cte2 AS (...)

-- Named logical steps

🔹 Basic CTE Example

A simple CTE creates a temporary result set that you can reference in the main query. This approach improves code organization and readability for complex operations.

-- Create sample table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

-- Insert sample data
INSERT INTO employees (name, department, salary) VALUES
    ('John Doe', 'IT', 75000),
    ('Jane Smith', 'IT', 80000),
    ('Bob Johnson', 'HR', 60000),
    ('Alice Brown', 'HR', 65000);

-- Use CTE to find average salary by department
WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.department, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department;

name        | department | salary   | avg_salary
------------+------------+----------+------------
John Doe    | IT         | 75000.00 | 77500.00
Jane Smith  | IT         | 80000.00 | 77500.00
Bob Johnson | HR         | 60000.00 | 62500.00
Alice Brown | HR         | 65000.00 | 62500.00

🔹 Multiple CTEs

You can define multiple CTEs in a single query by separating them with commas. Each CTE can reference previously defined CTEs, creating a logical flow of data transformations.

-- Multiple CTEs for complex analysis
WITH 
high_earners AS (
    SELECT * FROM employees WHERE salary > 70000
),
dept_count AS (
    SELECT department, COUNT(*) AS emp_count
    FROM high_earners
    GROUP BY department
)
SELECT * FROM dept_count ORDER BY emp_count DESC;

department | emp_count
-----------+-----------
IT         | 2

🔹 Recursive CTE

Recursive CTEs reference themselves to process hierarchical or tree-structured data. They consist of a base case and a recursive step that continues until no more rows are returned.

-- Create organizational hierarchy
CREATE TABLE org_chart (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    manager_id INTEGER
);

INSERT INTO org_chart (name, manager_id) VALUES
    ('CEO', NULL),
    ('CTO', 1),
    ('CFO', 1),
    ('Dev Lead', 2),
    ('Developer', 4);

-- Recursive CTE to show hierarchy
WITH RECURSIVE hierarchy AS (
    -- Base case: top-level employees
    SELECT id, name, manager_id, 1 AS level
    FROM org_chart
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: employees under managers
    SELECT o.id, o.name, o.manager_id, h.level + 1
    FROM org_chart o
    JOIN hierarchy h ON o.manager_id = h.id
)
SELECT 
    REPEAT('  ', level - 1) || name AS hierarchy,
    level
FROM hierarchy
ORDER BY level, name;

hierarchy      | level
---------------+-------
CEO            | 1
  CFO          | 2
  CTO          | 2
    Dev Lead   | 3
      Developer| 4

🔹 CTE for Data Modification

CTEs can be used with INSERT, UPDATE, and DELETE statements. This allows you to modify data based on complex query results while keeping the logic clear and organized.

-- Update using CTE
WITH top_performers AS (
    SELECT id FROM employees
    WHERE salary > 75000
)
UPDATE employees
SET salary = salary * 1.10
WHERE id IN (SELECT id FROM top_performers);

-- Delete using CTE
WITH inactive_users AS (
    SELECT id FROM employees
    WHERE department = 'Archived'
)
DELETE FROM employees
WHERE id IN (SELECT id FROM inactive_users);

🔹 CTE Benefits

Common Table Expressions offer several advantages over subqueries and temporary tables. They improve query maintainability, performance, and code clarity for complex database operations.