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.
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.
WITH CTE_NAME (number) AS ( -- Regular query SELECT 1 ) SELECT * FROM CTE_NAME;
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.
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
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.
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);
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;
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
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.