Working with Common Table Expressions (CTEs) in PostgreSQL

Introduction

Common Table Expressions (CTEs) in PostgreSQL provide a way to write complex queries in a more readable and maintainable manner. CTEs allow you to define temporary result sets that can be referenced within the main query, making SQL statements more structured and easier to understand.

This article explores:

• The basics of CTEs
• Using recursive and non-recursive CTEs
• Practical examples with the Reservations table

Understanding CTEs in PostgreSQL

A Common Table Expression (CTE) is defined using the WITH keyword, followed by a named query. The result of this query can then be used in subsequent queries.

Syntax:

WITH cte_name AS (
    SELECT column1, column2 FROM table_name WHERE condition
)
SELECT * FROM cte_name;

Using Non-Recursive CTEs

A non-recursive CTE is simply a named subquery that makes queries more readable.

Example:

WITH ConfirmedReservations AS (
    SELECT reservation_id, customer_id, total_rental_price
    FROM Reservations
    WHERE status = 'Confirmed'
)
SELECT * FROM ConfirmedReservations;

This query creates a temporary table of confirmed reservations and retrieves all its records, the output will be shown below :

carrentaldb=# WITH ConfirmedReservations AS (
    SELECT reservation_id, customer_id, total_rental_price
    FROM Reservations
    WHERE status = 'Confirmed'
)
SELECT * FROM ConfirmedReservations;
 reservation_id | customer_id | total_rental_price 
----------------+-------------+--------------------
              1 |           1 |             180.00
              5 |           5 |             200.00
              8 |           8 |             240.00
(3 rows)

Using Recursive CTEs

Recursive CTEs are useful for hierarchical data processing, such as organization structures or parent-child relationships.

Syntax:

WITH RECURSIVE cte_name AS (
    SELECT column1, column2 FROM table_name WHERE base_condition
    UNION ALL
    SELECT column1, column2 FROM table_name JOIN cte_name ON join_condition
)
SELECT * FROM cte_name;

Example:

WITH RECURSIVE ReservationChain AS (
    SELECT reservation_id, customer_id, pickup_date, return_date
    FROM Reservations
    WHERE reservation_id = 1  -- Base case
    UNION ALL
    SELECT r.reservation_id, r.customer_id, r.pickup_date, r.return_date
    FROM Reservations r
    JOIN ReservationChain rc ON r.customer_id = rc.customer_id
)
SELECT * FROM ReservationChain;

This query finds all reservations made by the same customer, starting from a given reservation.

Benefits of Using CTEs

• Improves query readability and organization
• Avoids redundant subqueries
• Enhances maintainability of complex queries
• Enables hierarchical and recursive queries

Conclusion

Common Table Expressions (CTEs) in PostgreSQL provide a powerful way to simplify complex queries, making them more readable and maintainable. By using non-recursive and recursive CTEs, you can enhance data processing efficiency and handle hierarchical relationships effectively. Mastering CTEs will significantly improve your PostgreSQL query-writing skills.