Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries in PostgreSQL

Introduction
The very basics
Infinite looping
Processing trees with recursive CTEs
Processing graphs with recursive CTEs
Summary

Instruction

Let's move on to another interesting exercise!

Exercise

Your task is to show the following columns:

  • id – The ID of each department.
  • name – The name of the department.
  • part_of – The ID of the department one level up.
  • path – All the department names from the root department until the given department, separated with slashes ('/').

Remember that the root department has a NULL value in part_of.

Stuck? Here's a hint!

The query we created for employees might help you here:

WITH RECURSIVE hierarchy AS (
  SELECT
    id,
    first_name,
    last_name,
    superior_id,
    last_name::text AS path 
  FROM employee 
  WHERE superior_id IS NULL
  UNION ALL 
  SELECT
    employee.id, 
    employee.first_name, 
    employee.last_name, 
    employee.superior_id, 
    hierarchy.path || '->' || employee.last_name
  FROM employee, hierarchy
  WHERE employee.superior_id = hierarchy.id
)

SELECT 
  * 
FROM hierarchy;