PostgreSQL Common Table Expressions and Recursive Queres

PostgreSQL Common Table Expressions, or CTE for short, provides a statement-level temporary table to help you build complex and clear SQL statements.

PostgreSQL CTE syntax

PostgreSQL common table expressions are defined using the WITH keyword, the following is the syntax of the PostgreSQL CTE:

WITH [RECURSIVE] cte_name [(cte_column_list)] AS (
    cte_definition
)
primary_statement;

Here:

• Commson table expressions begin with the WITH keyword.
• The RECURSIVE keyword indicates that this common table expression is recursive. It is optional.
• The cte_name is the name of the common table expression, which is equivalent to the table name of the temporary table.
• The cte_column_list is a list of column names for the common table expression, with multiple column names separated by commas. It is optional.
• The cte_definition is an statement for the common table expression, which can be a SELECT, INSERT, UPDATE, or DELETE statement.
• The primary_statement is the main statement, that uses the cte_name. It can be SELECT, INSERT, UPDATE, or DELETE.

Basic example of PostgreSQL common table expression

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

The film table in the Sakila sample database store films from DVD rental stores.

Suppose, you want to determine whether each films’s rent is higher than the average rent of the rating it locates. You can use a statement with a CTE like this:

WITH file_rating_avg AS (
  SELECT
    rating,
    avg(rental_rate) avg_rental_rate
  FROM film
  GROUP BY rating
)
SELECT
  f.film_id,
  f.title,
  f.rental_rate,
  a.avg_rental_rate,
  f.rental_rate > avg_rental_rate "Greater?"
FROM
  film f,
  file_rating_avg a
WHERE f.rating = a.rating
LIMIT 10;

 film_id |      title       | rental_rate |  avg_rental_rate   | Greater?
---------+------------------+-------------+--------------------+----------
       1 | ACADEMY DINOSAUR |        0.99 | 3.0518556701030928 | f
       2 | ACE GOLDFINGER   |        4.99 | 2.8888764044943820 | t
       3 | ADAPTATION HOLES |        2.99 | 2.9709523809523810 | t
       4 | AFFAIR PREJUDICE |        2.99 | 2.8888764044943820 | t
       5 | AFRICAN EGG      |        2.99 | 2.8888764044943820 | t
       6 | AGENT TRUMAN     |        2.99 | 3.0518556701030928 | f
       7 | AIRPLANE SIERRA  |        4.99 | 3.0348430493273543 | t
       8 | AIRPORT POLLOCK  |        4.99 | 2.9387179487179487 | t
       9 | ALABAMA DEVIL    |        2.99 | 3.0348430493273543 | f
      10 | ALADDIN CALENDAR |        4.99 | 2.9709523809523810 | t
(10 rows)

First, we define a common table expression named file_rating_avg:

WITH file_rating_avg AS (
  SELECT
    rating,
    avg(rental_rate) avg_rental_rate
  FROM film
  GROUP BY rating
)

Then, we use file_rating_avg in the main statement as following:

SELECT
  f.film_id,
  f.title,
  f.rental_rate,
  a.avg_rental_rate,
  f.rental_rate > avg_rental_rate "Greater?"
FROM
  film f,
  file_rating_avg a
WHERE f.rating = a.rating
LIMIT 10;

PostgreSQL common table expression recursive query

PostgreSQL common table expressions support recursive queries, which are very suitable for some scenarios that store tree data, such as product categories, system menus, and so on.

Next, we design a table for storing product categories, and then use PostgreSQL common table expressions to get the specified category and all sub-categories of this category.

We’re going to demonstrate this example in the testdb database. Please use the following statement to create the database testdb first:

CREATE DATABASE testdb;

Connect to the testdb database using the following statement:

\c testdb;

Create a product category table named category using the following statement:

DROP TABLE IF EXISTS category;
CREATE TABLE category (
  id SERIAL PRIMARY KEY,
  name VARCHAR NOT NULL,
  parent_id INT,
  CONSTRAINT fk_category
    FOREIGN KEY(parent_id) REFERENCES category(id)
);

We created the category table with three columns id, name, parent_id, and the parent_id column stores the upper-level classification id. Note that here we used a foreign key constraint.

Insert some rows containing product category information using the following statement:

INSERT INTO category (id, name, parent_id)
VALUES
  (1, 'ROOT', NULL),
  (2, 'Baby', 1),
  (3, 'Home And Kitchen', 1),
  (4, 'Baby Care', 2),
  (5, 'Feeding', 2),
  (6, 'Gifts', 2),
  (7, 'Safety', 2),
  (8, 'Bedding', 3),
  (9, 'Bath', 3),
  (10, 'Furniture', 3),
  (11, 'Grooming', 4),
  (12, 'Hair Care', 4),
  (13, 'Baby Foods', 5),
  (14, 'Food Mills', 5),
  (15, 'Solid Feeding', 5),
  (16, 'Bed Pillows', 8),
  (17, 'Bed Skirts', 8);

Suppose, you want to query the category id of 2 and all its subcategories, use the following statement:

WITH RECURSIVE cte_categories AS (
  SELECT
    id,
    name,
    parent_id
  FROM category
  WHERE id = 2
  UNION
  SELECT
    c.id,
    c.name,
    c.parent_id
  FROM category c, cte_categories cs
  WHERE cs.id = c.parent_id
)
SELECT *
FROM cte_categories;

 id |     name      | parent_id
----+---------------+-----------
  2 | Baby          |         1
  4 | Baby Care     |         2
  5 | Feeding       |         2
  6 | Gifts         |         2
  7 | Safety        |         2
 11 | Grooming      |         4
 12 | Hair Care     |         4
 13 | Baby Foods    |         5
 14 | Food Mills    |         5
 15 | Solid Feeding |         5
(10 rows)

We define a CTE cte_categories by WITH RECURSIVE, and then we find rows from the CTE cte_categories.

Conclusion

In this article we showed what PostgreSQL’s common table expressions can do. By using common table expressions, you can simplify your complex SQL statements and also use recursive queries.