PostgreSQL – CTE

In PostgreSQL, the CTE(Common Table Expression) is used as a temporary result set that the user can reference within another SQL statement like SELECT, INSERT, UPDATE or DELETE. CTEs are temporary in the sense that they only exist during the execution of the query. CTEs are typically used to simplify complex joins and subqueries in PostgreSQL.

Syntax:
WITH cte_name (column_list) AS (
    CTE_query_definition 
)
statement;

Let’s analyze the above syntax.

• The first step is to set the name of the CTE followed by an optional column list.
• Then we specify a query that returns the result set within the body of WITH clause. if not specified explicitly then the select list of the CTE_query_definition will become the column list of the CTE.
• Finally use the CTE like a table or view in the statement which can be a SELECT, INSERT, UPDATE, or DELETE.

For examples we will be using the sample database (ie, dvdrental).

Example 1:
In this, we will define a common table expression named cte_film using the WITH clause with the film and rental table as follows to determine the length of the films.

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:

Example 2:
The following statement illustrates the use of the CTE with the RANK() window function in the film table to rank the films based on their length as follows:

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: