Common Table Expression

Introduction

A Common Table Expression (CTE) is considered a temporary table variable that only exists within the scope of a single query statement (SELECT, INSERT, UPDATE or DELETE). It helps you encapsulate a complex query logic cluster into a variable for reuse immediately below.
In Postgres, the WITH clause is used as the syntax keyword to define a CTE.

Features

The scope of existence is only within a single query statement.
Stored data is typically processed in RAM (in-memory) or temporarily saved as a file if the data is too large.
No need to clean up (drop) after execution, it will automatically disappear as soon as the statement finishes executing.
A special feature is the support for recursion (WITH RECURSIVE), which is highly powerful when processing tree or hierarchical data structures.

Use cases

The following are suitable use cases for using a CTE including

You want to write clean and readable code, breaking down complex JOIN statements into individual steps.
Small or moderate intermediate data.
You need recursive queries such as finding parent-child directory trees or organizational charts.
You only need to use that dataset exactly once in the current query.

Detail

Create the tables as follows

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES categories(id) ON DELETE CASCADE
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category_id INT REFERENCES categories(id),
    status VARCHAR(20) DEFAULT 'active'
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    total_amount DECIMAL(10, 2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

WITH

After seeding the data, you can use the CTE as follows

WITH 
vip_customers AS (
    SELECT customer_id, SUM(total_amount) as total_spent
    FROM orders
    WHERE status = 'COMPLETED'
    GROUP BY customer_id
    HAVING SUM(total_amount) > 500
),

latest_orders AS (
    SELECT DISTINCT ON (customer_id) id AS order_id, customer_id, total_amount, created_at
    FROM orders
    WHERE status = 'COMPLETED'
    ORDER BY customer_id, created_at DESC
)

SELECT 
    c.id AS customer_id,
    c.name AS customer_name,
    v.total_spent,
    lo.order_id AS latest_order_id,
    lo.total_amount AS latest_order_amount,
    lo.created_at AS latest_order_date
FROM vip_customers v
INNER JOIN customers c ON v.customer_id = c.id
INNER JOIN latest_orders lo ON v.customer_id = lo.customer_id;

This query is used to retrieve the latest orders of customers who have a total payment greater than 500.
You can see that separating the logic into two tables vip_customers and latest_orders makes the query simpler and easier to understand, ensuring effortless maintenance in the future.

WITH RECURSIVE

WITH RECURSIVE sub_categories AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = 1
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN sub_categories sc ON c.parent_id = sc.id
)
SELECT p.id, p.name AS product_name, p.price, sc.name AS category_name
FROM products p
INNER JOIN sub_categories sc ON p.category_id = sc.id;

The query above uses recursion to get all products that have category id 1 (including products belonging to its subcategories).

Starting with retrieving information for category id 1, the result is stored in the sub_categories table.
Then, it joins the sub_categories table with the categories table to get all categories where sub_categories.id = categories.id, the result is stored back into the sub_categories table.
This process continues joining the sub_categories table with the categories table as described above until no more results are found.
After executing WITH RECURSIVE, we will have all the data in the sub_categories table containing an aggregation of all rows that are subcategories of category id 1.
Finally, it joins the sub_categories table with the products table by category.

INSERT

WITH sampled_category AS (
    SELECT category_id 
    FROM products 
    WHERE name = 'Product 10'
    LIMIT 1
)
INSERT INTO products (name, price, category_id)
SELECT 'Product 10 new', 1400.00, category_id
FROM sampled_category
RETURNING *;

Adding a new product with the same category as another product.

Retrieve the category of a single product by product name.
Then perform an INSERT using that category information.
Use RETURNING to immediately show the information of the newly inserted product.

UPDATE

WITH RECURSIVE sub_categories AS (
    SELECT id FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id FROM categories c
    INNER JOIN sub_categories et ON c.parent_id = et.id
)
UPDATE products
SET price = price * 0.90
WHERE category_id IN (SELECT id FROM sub_categories)
RETURNING id, name, price AS new_price;

Offering a 10% discount on all products belonging to a specific category.

Use recursion to retrieve all subcategories of category id 1.
Use UPDATE to modify the price, reducing it by 10% for products belonging to those categories.

DELETE

WITH RECURSIVE categories_to_delete AS (
    SELECT id FROM categories WHERE id = 5
    UNION ALL
    SELECT c.id FROM categories c
    INNER JOIN categories_to_delete ctd ON c.parent_id = ctd.id
),
update_products AS (
    UPDATE products
    SET status = 'inactive', category_id = null
    WHERE category_id IN (SELECT id FROM categories_to_delete)
    RETURNING category_id
)
DELETE FROM categories
WHERE id IN (SELECT id FROM categories_to_delete)
RETURNING *;

When deleting a category, its subcategories will be deleted and products belonging to that category will be switched to inactive.

Use recursion to retrieve all subcategories of category id 2.
Change the status of products belonging to categories_to_delete to inactive.
Then delete the categories belonging to categories_to_delete.