PostgreSQL recursive query explained with a bare minimum dataset and example

Data Example

grandfather 
father
child1
child2
create table people (id integer, nome varchar, parent_id integer);
INSERT INTO people VALUES
(1,'grandfather', null),
(2,'father', 1),
(3,'child1', 2),
(4,'child2', 2),
(5,'other person non connected to the hierarchy', null);

Query to return all the hierarchy

WITH RECURSIVE x AS (
-- base case starting from grandfather
SELECT
id,
parent_id,
nome
FROM
people
WHERE
id =1
UNION

--- recursive query (note it adds to the partial table "x")

SELECT
p.id,
p.parent_id,
p.nome
FROM
people p
INNER JOIN x x1
ON p.parent_id = x1.id
) SELECT
*
FROM
x ;
+----+-----------+-------------+
| id | parent_id | nome |
|----+-----------+-------------|
| 1 | <null> | grandfather |
| 2 | 1 | father |
| 3 | 2 | child1 |
| 4 | 2 | child2 |
+----+-----------+-------------+

How it works

+----+-----------+-------------+
| id | parent_id | nome |
|----+-----------+-------------|
| 1 | <null> | grandfather | <-- added as id=1
+----+-----------+-------------+
+----+-----------+-------------+
| id | parent_id | nome |
|----+-----------+-------------|
| 1 | <null> | grandfather |
| 2 | 1 | father | <-- added as its parent_id=1
+----+-----------+-------------+
+----+-----------+-------------+
| id | parent_id | nome |
|----+-----------+-------------|
| 1 | <null> | grandfather |
| 2 | 1 | father |
| 3 | 2 | child1 | <-- added as its parent_id=2
| 4 | 2 | child2 | <-- added as its parent_id=2
+----+-----------+-------------+

Links

--

--

--

Software Engineer @ London [https://www.linkedin.com/in/elvisciotti]

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

PyTest and PageObjects For the Win

Banking App With FastApi and Tkinter

Banking App With FastApi and Tkinter

D&T — Regenerative Architecture

Slashing needed Permissions in Istio

MY EXPERIENCE SO FAR AT CODEVIXENS ACADEMY. LEARNING BACKEND WEB DEVELOPMENT.

Programming is fun. Although there is a need for commitment, dedication and interest.

5 DevOps Practices You Should Be Following

Post-mortem: December 7th

Abstraction in software (part 1)

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Elvis Ciotti

Elvis Ciotti

Software Engineer @ London [https://www.linkedin.com/in/elvisciotti]

More from Medium

Change Data Capture with Debezium and SQL Server

PostgreSQL EXPLAIN — What are the Query Costs?

SQL Fundamental

Let’s have a look at the PostgreSQL CRUD operation

PostgreSQL