PostgreSQL Common Table Expression (CTE) (original) (raw)

Last Updated : 15 Jul, 2025

In **PostgreSQL, **Common Table Expressions (CTEs) are a powerful feature that allows us to define temporary result sets that can be referenced within other **SQL statements. This includes statements like **SELECT, **INSERT, **UPDATE, or **DELETE. **CTEs make complex queries more readable and maintainable by breaking them into modular, reusable subqueries.

The use of **CTEs in **PostgreSQL simplifies working with **recursive queries and complex joins, thus improving performance and query organization. This article explains how to use **Common Table Expressions (CTEs) in **PostgreSQL, complete with syntax, examples, and best practices.

PostgreSQL – Common Table Expression (CTE)

A CTE (Common Table Expression) is a temporary result set that we can define within the execution scope of a query. Defined using the WITH clause, a **CTE can be referenced multiple times in a SQL statement, improving query performance and readability.

It’s particularly useful for simplifying **complex queries, especially those involving **multiple subqueries. In **PostgreSQL, **CTEs can also be **recursive, making them an excellent choice for **hierarchical or tree-like data structures, such as **organizational charts or file systems.

**Syntax

WITH cte_name (column_list) AS (
CTE_query_definition
)
statement;

**Key Terms

**CTE Name: The first step is to set the name of the CTE, followed by an optional column list.
**CTE Query Definition: Specify a query that returns the result set within the body of the WITH clause. If the column list is not specified, the select list of the CTE_query_definition will become the column list of the CTE.
**Usage: Finally, use the CTE like a table or view in the statement, which can be a SELECT, INSERT, UPDATE, or DELETE.

Examples of PostgreSQL CTE

For examples below, we will be using the sample database (ie, dvdrental). Let’s explore some real-world **PostgreSQL CTE examples to understand their functionality and use cases

**Example 1: Categorizing Films by Length

In this example, we will define a CTE named **cte_film**using the WITH clause with the film table. We will categorize films based on their length as '**Short', '**Medium', or '**Long'.

**Query:

WITH cte_film AS (
SELECT
film_id,
title,
(CASE
WHEN length < 30 THEN 'Short'
WHEN length < 90 THEN 'Medium'
ELSE 'Long'
END) length
FROM
film
)
SELECT
film_id,
title,
length
FROM
cte_film
WHERE
length = 'Long'
ORDER BY
title;

**Output

Categorizing Films by Length

**Explanation:

In this query, the **CTE cte_film first categorizes films based on their length. The main SELECT query then filters these films to only show those categorized as 'Long'. This makes the query simpler and more readable.

**Example 2: Ranking Films by Length Using the RANK() Function

In this example, we will use the CTE with the RANK() window function in the film table to **rank the films based on their length. The films will be ranked within each rating category.

**Query:

WITH cte_film AS (
SELECT film_id,
title,
rating,
length,
RANK() OVER (
PARTITION BY rating
ORDER BY length DESC)
length_rank
FROM
film
)
SELECT *
FROM cte_film
WHERE length_rank = 1;

**Output

Ranking Films by Length Using the RANK() Function

**Explanation:

In this query, the **CTE cte_film calculates the rank of each film within its rating category based on its length. The RANK() window function ranks films in descending order of their length. The main query then selects only the **highest-ranked films (length_rank = 1)

Important Points About PostgreSQL Common Table Expressions (CTEs)

**CTEs are used to define temporary result sets that exist only during the execution of a query.
The **WITH**keyword is used to define a CTE, followed by the CTE name and an optional column list.
**CTEs can be used with SELECT, INSERT, UPDATE, and DELETE statements.
**PostgreSQL supports **recursive CTEs, which are useful for working with hierarchical data, such as organizational structures or tree models.

Conclusion

**Common Table Expressions (CTEs) are a powerful feature in PostgreSQL that enable us to simplify **complex queries, improve readability, and enhance SQL code maintainability. Whether we're using **CTEs for **recursive queries, **window functions, or simplifying joins, they are an essential tool in any **PostgreSQL developer's toolkit.