Introduction to PostgreSQL common table expression (CTE)

A common table expression (CTE) allows you to create a temporary result set within aquery.

A CTE helps you enhance the readability of a complex query by breaking it down into smaller and more reusable parts

Here’s the basic syntax for creating a common table expression:

WITH cte_name (column1, column2, ...)AS (    -- CTE query    SELECT ...)-- Main query using the CTESELECT ...FROM cte_name;

In this syntax:

• WITH clause: Introduce the common table expression (CTE). It is followed by the name of the CTE and a list of column names in parentheses. The column list is optional and is only necessary if you want to explicitly define the columns for the CTE.
• CTE name: Specify the name of the CTE. The CTE name exists within the scope of the query. Ensure that the CTE name is unique within the query.
• Column List (optional): Specify the list of column names within the parentheses after the CTE name. If not specified, the columns implicitly inherit the column names fromSELECT statement inside the CTE.
• AS keyword: The AS keyword indicates the beginning of the CTE definition.
• CTE query: This is a query that defines the CTE, which may includeJOINs,WHERE,GROUP BY clauses, and other valid SQL constructs.
• Main query: After defining the CTE, you can reference it in the main query by its name. In the main query, you can use the CTE as if it were a regular table, simplifying the structure of complex queries.

PostgreSQL CTE examples

Let’s explore some examples of using common table expressions (CTE).

1) Basic PostgreSQL common table expression example

The following example uses a common table expression (CTE) to select thetitle andlength of films in the'Action' category and returns all the columns of the CTE:

WITH action_filmsAS (  SELECT    f.title,    f.length  FROM    film f    INNER JOIN film_category fcUSING (film_id)    INNER JOIN category cUSING(category_id)  WHERE    c.name= 'Action')SELECT * FROM action_films;

Output:

title          | length-------------------------+-------- Amadeus Holy            |    113 American Circus         |    129 Antitrust Tomatoes      |    168 Ark Ridgemont           |     68...

In this example:

• First, the CTE query combines data from three tablesfilm,film_category, andcategory using theINNER JOIN clauses.
• Then, the main query retrieves data from theaction_films CTE using a simpleSELECT statement.

2) Join a CTE with a table example

We’ll use therental andstaff tables from thesample database in this example:

The following example join a CTE with a table to find the staff and rental count for each:

WITH cte_rental AS (  SELECT    staff_id,    COUNT(rental_id) rental_count  FROM    rental  GROUP BY    staff_id)SELECT  s.staff_id,  first_name,  last_name,  rental_countFROM  staff s  INNER JOIN cte_rental USING (staff_id);

In this example:

• First, the CTE returns a result set that includes the staff id and the rental counts.
• Then, the main query joins thestaff table with the CTE using thestaff_id column.

Output:

staff_id | first_name | last_name | rental_count----------+------------+-----------+--------------        1 | Mike       | Hillyer   |         8040        2 | Jon        | Stephens  |         8004(2 rows)

3) Multiple CTEs example

The following example uses multiple CTEs to calculate various statistics related to films and customers:

WITH film_stats AS (    -- CTE 1: Calculate film statistics    SELECT        AVG(rental_rate) AS avg_rental_rate,        MAX(length) AS max_length,        MIN(length) AS min_length    FROM film),customer_stats AS (    -- CTE 2: Calculate customer statistics    SELECT        COUNT(DISTINCT customer_id) AS total_customers,        SUM(amount) AS total_payments    FROM payment)-- Main query using the CTEsSELECT    ROUND((SELECT avg_rental_rate FROM film_stats), 2) AS avg_film_rental_rate,    (SELECT max_length FROM film_stats)AS max_film_length,    (SELECT min_length FROM film_stats)AS min_film_length,    (SELECT total_customers FROM customer_stats)AS total_customers,    (SELECT total_payments FROM customer_stats)AS total_payments;

Output:

avg_film_rental_rate | max_film_length | min_film_length | total_customers | total_payments----------------------+-----------------+-----------------+-----------------+----------------                 2.98 |             185 |              46 |             599 |       61312.04(1 row)

In this example, we create two CTEs:

• film_stats: Calculates statistics related to films including the average rental rate, maximum length, and minimum length.
• customer_stats: Calculates statistics related to customers including the total number of distinct customers and the overall payments made.

The main query retrieves specific values from each CTE to create a summary report.

PostgreSQL CTE advantages

The following are some advantages of using common table expressions or CTEs:

• Improve the readability of complex queries. You use CTEs to organize complex queries in a more organized and readable manner.
• Ability to createrecursive queries, which are queries that reference themselves. The recursive queries come in handy when you want to query hierarchical data such as organization charts.
• Use in conjunction withwindow functions. You can use CTEs in conjunction with window functions to create an initial result set and use another select statement to further process this result set.

Summary

• Use a common table expression (CTE) to create a temporary result set within a query.
• Leverage CTEs to simplify complex queries and make them more readable.

Last updated onJanuary 22, 2024

On this page

• Introduction to PostgreSQL common table expression (CTE)
• PostgreSQL CTE examples
• PostgreSQL CTE advantages
• Summary