select version();

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

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);

SELECT * FROM folders;

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;

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;