clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335553 fiddles created (27410 in the last week).

select version();
version
PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
 hidden batch(es)


CREATE TABLE folders ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, parent_id INT )
 hidden batch(es)


INSERT INTO folders(name, parent_id) VALUES ('home', NULL), ('root', NULL), ('user', 1), ('documents', 3), ('pictures', 3), ('downloads', 3), ('vacations', 5), ('baltic sea', 7), ('tatra mountains', 7);
9 rows affected
 hidden batch(es)


SELECT * FROM folders;
id name parent_id
1 home
2 root
3 user 1
4 documents 3
5 pictures 3
6 downloads 3
7 vacations 5
8 baltic sea 7
9 tatra mountains 7
 hidden batch(es)


WITH RECURSIVE subfolders AS ( SELECT id, name, NULL AS parent FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, s.name FROM folders f INNER JOIN subfolders s ON f.parent_id = s.id ) SELECT * FROM subfolders;
id name parent
1 home
2 root
3 user home
4 documents user
5 pictures user
6 downloads user
7 vacations pictures
8 baltic sea vacations
9 tatra mountains vacations
 hidden batch(es)


WITH RECURSIVE childs AS ( SELECT id, name, parent_id FROM folders WHERE name = 'baltic sea' UNION ALL SELECT f.id, f.name, f.parent_id FROM folders f INNER JOIN childs c ON f.id = c.parent_id ) SELECT id, name FROM childs;
id name
8 baltic sea
7 vacations
5 pictures
3 user
1 home
 hidden batch(es)