Skip to content

Instantly share code, notes, and snippets.

@mauricioszabo

mauricioszabo/neverending.psql

Created January 7, 2021 12:49
Show Gist options
  • Save mauricioszabo/e2d26a617f68787b4081c8e62559ce18 to your computer and use it in GitHub Desktop.
Save mauricioszabo/e2d26a617f68787b4081c8e62559ce18 to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL example of recursive queries
Raw
neverending.psql
INSERT INTO tree (id, parent_id, description)
VALUES
(6, 6, 'Im Recursive');
WITH RECURSIVE parents as (
select id,
description,
parent_id
from tree
where parent_id = 6
union all
select child.id,
child.description,
child.parent_id
from tree as child
join parents on parents.id = child.parent_id
)
select * from parents;
Raw
queries.psql
CREATE TABLE tree(
id SERIAL PRIMARY KEY,
parent_id INTEGER,
description VARCHAR
);
INSERT INTO tree (id, parent_id, description)
VALUES
(1, null, 'level 0'),
(2, 1, 'level 1'),
(3, 2, 'level 2'),
(4, null, 'Another level 0'),
(5, 4, 'Another level 1');
WITH RECURSIVE parents as (
select id,
description,
parent_id
from tree
where parent_id IS NULL
union all
select child.id,
child.description,
child.parent_id
from tree as child
join parents on parents.id = child.parent_id
)
select * from parents;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment