Command Table Expressions (CTEs) in PostgreSQL

What are CTEs in PostgreSQL?

CTE or Command Table Expression in PostgreSQL is the feature that allow you to create temporary result sets within a query. In simple words, CTEs in PostgreSQL are temporary result sets that can be referenced within another SQL statement, such as SELECT, INSERT, UPDATE, or DELETE.

CTEs are temporary. It means they only exist during the execution of the query. They are useful for simplifying complex joins and subqueries in PostgreSQL. By defining a CTE in PostgreSQL, you can create a named query that can be referenced multiple times within the same query, reducing redundancy and improving query organization.

Command Table Expression in PostgreSQL are like subqueries, but with more flexibility and readability. They provide a way to break down complex queries into smaller, more manageable parts, making them easier to read and understand. They are useful when dealing with recursive queries, complex data transformations, and query optimization.

Syntax

The syntax for creating Command Table Expression in PostgreSQL involves using the WITH keyword. It is then followed by a unique name for the CTE and an optional column list. The CTE name is then followed by the AS keyword and a query that defines the CTE. The basic syntax example of CTE in PostgreSQL is given below :

Let's break down the syntax:

• ctename_ is the name you give to the CTE.
• (column1, column2, ...) is a list of column names. It is used to define the column names explicitly. (optional)
• CTE query definition goes here is where you write the actual query that defines the CTE. It can be any valid SELECT statement.
• table_name is the name of the table you want to query in the CTE.
• condition is an optional condition that you can use to filter the rows in the CTE.
• The main query can reference the CTE by its name (cte_name), allowing you to perform further operations on the CTE's result set.

Examples

Let's understand Command Table Expression in PostgreSQL with the help of examples given below.

Example 1: Recursive CTE for Hierarchical Data

The CTE named factorial_sequence is defined with two columns: n and factorial. The initial row in the CTE is the base case, where n is set to 1 and factorial is set to 1.

The recursive step is defined using the UNION ALL clause. It selects the next value of n by adding 1 to the previous value, and calculates the factorial by multiplying the current value of n with the previous factorial value. This recursive step continues until the condition specified in the WHERE clause is met, which limits the sequence up to 9! for simplicity.

Finally, the main query selects the values of n and factorial from the factorial_sequence CTE, resulting in the factorial sequence up to the specified limit. The output of the query is given below.

Output:

Example 2: CTE with JOIN

In this example, we create a CTE called cte_sales that calculates the total sales quantity for each product by grouping the sales table by product_id. Then, we join the CTE with the products table to retrieve the product name and total sales for each product. This allows us to combine the results of the CTE with other tables in a single query. The output of query is given below.

Output:

Joining a CTE with a Table

To join a CTE with a table in PostgreSQL, you can treat the CTE as if it were a regular table. You can reference the CTE in the FROM clause of your query and join it with other tables using the appropriate join conditions. Let's understand with the help of at an example:

Suppose we have two tables: employees and salaries. The employees table contains information about employees, while the salaries table contains their salary details. We want to find the employees whose salaries are above a certain threshold. Here's how we can achieve this using a CTE.

In the above example, we first create a CTE named high_earning_employees that selects the employee_id, first_name, and last_name from the employees table based on a salary threshold (here 50000). Then, we join this CTE with the salaries table using the employee_id column. The output of the query is given below.

Output:

How to Use CTE with a Window Function?

Window Functions are a powerful feature in PostgreSQL that allow you to perform calculations across a set of rows, called a window, without reducing the result set. These functions can be used to calculate running totals, rankings, moving averages, and more. Window Functions are typically used in conjunction with the OVER clause, which defines the window over which the calculation is performed.

Combining CTEs with Window Functions

By combining CTEs with Window Functions, you can create complex queries that involve multiple calculations and aggregations. The CTE acts as a temporary table that can be referenced by subsequent queries, allowing you to perform calculations on the result set of the CTE using Window Functions.

Let's consider a scenario where we have a table called sales with columns product_name, sale_date, and revenue. We want to calculate the cumulative revenue for each product over time. Here's how you can achieve this using CTEs and Window Functions in PostgreSQL:

In the above code, we first define a CTE named cte_sales that selects the columns we need from the sales table. We then use the SUM() function with the OVER clause to calculate the cumulative revenue for each product. The PARTITION BY clause ensures that the calculation is performed separately for each product, while the ORDER BY clause specifies the order in which the rows are processed. Finally, we select the desired columns from the CTE and order the result set by product name and sale date. The output of the query is given below.

Output:

Advantages of Using CTE in PostgreSQL

Using Command Table Expression in PostgreSQL offers several advantages. Some of them are given below:-

• CTEs enhance the readability of complex queries by breaking them down into smaller, and manageable parts.
• CTEs promote code reusability by allowing queries to be defined once and reused multiple times.
• Recursive queries can be performed using CTEs, which are useful for working with hierarchical or tree-like data structures.
• CTEs can replace complex subqueries, improving code readability and query optimization.
• CTEs provide a convenient way to debug and test queries by isolating and testing individual CTEs independently.
• CTEs enable the reuse of intermediate results, reducing redundancy and improving efficiency.