Common Table Expressions (CTEs) and its examples in PostgreSQL

A Common Table Expression (CTE) is a name given to a temporary result set of a query that is required in the context of a more significant query. These temporary result sets are part of the query that can reference within SELECT, UPDATE, INSERT or DELETE statements. Common Table Expression is not stored anywhere, they only exist for the duration till the more significant query is executed. Common Table Expression enables us to write a complex query in a more readable and simplified format.

Syntax:

WITH cte_name  (column_names, … ) AS (
   CTE Query definition
)
SELECT …. ( MAIN QUERY )

Common Table Expression is defined using WITH clause, there can be more than one CTEs defined in one SQL statement separate by commas. Postgres doesn’t allow to have more than one WITH statement in one query.

WITH cte_name  (column_names, … ) AS (
CTE Query definition
),
cte_name2  (column_names, … )  AS (
 CTE Query definition
)
SELECT …. ( MAIN QUERY )

Common Table Expressions can be divided into two categories non-recursive and recursive:

Non-Recursive Common Table Expression

Non-Recursive CTEs can be used when we want to group data into a single name and return it as one result set then we can use this result set in our queries. They have several advantages over regular queries as they are used to simplify complex joins and subqueries.

Syntax:

WITH
  CTE_NAME (number) AS (
    -- Regular query
    SELECT
      1
  )
SELECT
  *
FROM
  CTE_NAME;

Output:

Recursive Common Table Expressions

A recursive common table expression (CTEs) is a CTE that references itself, or recursively calls itself. By this CTEs repeatedly executes and return subsets of data until the complete result set of data is returned.  Recursive common table expressions have the anchor member and the recursive member within the simple query.

Syntax:

WITH
  RECURSIVE CTE_NAME (number) AS (
    -- 	Anchor Member
    SELECT
      1 AS number
    UNION ALL
    -- 	Recursive Member
    SELECT
      number + 1
    FROM
      CTE_NAME
    WHERE
      number <= 5
  )
SELECT
  number
FROM
  CTE_NAME;

Common Table Expression In Postgres

Common Table Expression in Postgres uses WITH clause which allows assigning names to the sub-query and using the result set in the main query. PostgreSQL provides the WITH and AS statement that supports the designing of auxiliary queries also known as CTEs (Common Table Expressions). This WITH query is evaluated only once per execution of the main query, even if it refers to multiple times in the main query.

CTE Syntax in Postgres

WITH CTE_NAME (COL_LIST) AS (
	CTE_QUERY
)
STATEMENT;

Analyzing the above syntax :

• The WITH clause is used to define a temporary relation using a temporary query
• CTE_NAME stores result set from the temporary query
• In the above query, COL_LIST is a list of columns in the result set
• STATEMENT is our main query, Which uses the result set stored CTE_NAME

In the above CTE syntax, as we have seen column list is defined beside the CTE name, Postgres also allows defining this column list within sub-query

WITH CTE_NAME AS (
	CTE_QUERY AS COL_LIST
)
STATEMENT;

Postgres CTE examples

For understanding CTEs in Postgres in a better way. Let’s consider an example e-commerce shop that has a table called orders that contains stores sales data of each product.

For creating Orders Table

CREATE TABLE
  orders (
    id SERIAL PRIMARY KEY,
    order_number INT NOT NULL,
    product_name VARCHAR(10) NOT NULL,
    order_date DATE DEFAULT CURRENT_DATE,
    total_price INT NOT NULL
  );

Now using Non-Recursive CTEs, we will find the total price and count of each product which is sold using this order table

WITH
  CTE_NAME (product_name, total_count, total_price) AS (
    SELECT
      product_name,
      COUNT(product_name),
      SUM(total_price)
    FROM
      orders
    GROUP BY
      product_name
  )
SELECT
  *
FROM
  CTE_NAME;

Understanding Above Code

The above PostgreSQL code has a CTE under the name CTE_NAME which selects three columns product_name, total_count, and total_price from the orders table, subquery used GROUP BY statement over product_name to find these columns and then used in the main query to get the following output

Output:

Recursive Common Table Expression In Postgres

In recursive CTEs, the subquery within CTEs expression calls its own result set. They can be used for querying hierarchical data such as tracking lineage or finding organizational structure in the data sets. Postgres uses the RECURSIVE keyword in an order to define recursive CTEs.

Recursive CTE Syntax in Postgres

WITH
  RECURSIVE CTE_NAME AS(
    CTE_query_definition -- anchor member
    UNION
    [ ALL ] CTE_query CTE_NAME -- recursive member
  )
SELECT
  *
FROM
  CTE_NAME;

Analyzing the above syntax:

• Postgres uses the RECURSIVE keyword in an order to define recursive CTEs
• Anchor member works like a base condition for termination of these recursive calls
• One or more subquery definitions in CTEs are joined with the non-recursive term through are UNION or UNION ALL operator.
• The recursive member calls its own result set until it gets the complete result set.

Example of Factorial program using recursive CTEs in Postgres

WITH
  RECURSIVE CTE_NAME (line_number, factorial) AS (
    VALUES
      (0, 1) -- base subquery
    UNION ALL
    SELECT
      line_number + 1,
      (line_number + 1) * factorial
    FROM
      CTE_NAME -- recursive subquery
  )
SELECT
  *
FROM
  CTE_NAME
LIMIT
  5;

The following code uses Recursive CTE to get factorial series with anchor values (0, 1) and UNION ALL statement to join both anchor and recursive subquery together. While in the main query, we have a LIMIT statement to define the number of rows to display.

Output:

Postgres Recursive CTE Example

Recursive CTEs are also good when we have hierarchical data in our table. To understand this let us consider an example of an E-Commerce store where we have different categories of products.

This flowchart shows the E-Commerce store under which we have clothing and accessories sections and clothing in the parent of Men and women's clothing. In the same way, Grooming and Footwear are children of accessories.

Creating e-commerce Table

CREATE TABLE
  ecommerce (
    id SERIAL PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    level INT NOT NULL,
    parent_id INT NOT NULL
);

Inserting values in the above table

INSERT INTO
  ecommerce (id, name, level, parent_id)
VALUES
  (0, 'E Commerce store', 1, NULL),
  (1, 'Clothing', 2, 0),
  (2, 'Accessories', 2, 0),
  (3, 'Men Clothing', 3, 1),
  (4, 'Women Clothing', 3, 1),
  (5, 'Grooming', 3, 2),
  (6, 'Footwear', 3, 2);

Output:

Now Let’s look at the query to fetch all children for the given id, for this, we will define a subquery with an Anchor member with the root node id and union it with the recursive member, in an order to fetch all children's root nodes.

WITH
  RECURSIVE CTE_NAME AS (
    SELECT
      id,
      name,
      level,
      parent_id
    FROM
      ecommerce e1
    WHERE
      e1.id = 1 -- root node id ( base query )
    UNION
    SELECT
      e2.id,
      e2.name,
      e2.level,
      e2.parent_id
    FROM
      ecommerce e2
      JOIN CTE_NAME ON e2.parent_id = CTE_NAME.id -- recursive subquery ( to get all children of the root node )
  )
SELECT
  *
FROM
  CTE_NAME;

Output:

Basic Guidelines For Writing a Good CTE Query

• CTEs must be followed by a single Data Manipulation Language (DML) statements such as SELECT, INSERT, UPDATE, or DELETE that references the CTEs column
• Multiple CTEs can be defined in one query using a comma
• A CTE can refer to itself or other CTE in the same query
• Only one WITH Clause can be defined in a single query
• Recursive CTE might result in a loop, to avoid this loop LIMIT statement can be used

Conclusion

CTEs can be used instead of views and join which allows us to simplify for readability and maintainability of code. CTEs are a powerful tool, that enables solving some problems in a very simple and elegant way. Recursive CTEs are used in places where tables have hierarchical data or we need to track lineage within data.