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.
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;
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;