Postgres recursive LTREE lookup

Question

I have the following two tables that represent a nested folder hierarchy

Table "folder" contains basic folder information

+----+--------------+
| id |     name     |
+----+--------------+
|  1 | Top          |
|  2 | Science      |
|  3 | Astronomy    |
|  5 | Astrophysics |
|  6 | Cosmology    |
+----+--------------+

Table "folder_tree" contains the hierarchy structure, using the folder ids and column path being LTREE type.

+----+--------+---------+
| id | folder | path    |
+----+--------+---------+
| 32 | 1      | 1       |
| 33 | 2      | 1.2     |
| 36 | 3      | 1.2.3   |
| 37 | 4      | 1.2.3.4 |
| 38 | 5      | 1.2.3.5 |
+----+--------+---------+

I’m looking to validate an exact folder structure exists, when given an array of the structure for example how to validate [‘Top’, ‘Science’, ‘Astronomy’,’ Astrophysics’] in that order.

I believe a recursive query could work, where it looks at the root folder ‘Top’ first then works its way down to Astrophysics, confirming each folder exists along the way.

Can this be achieved with a recursive query? Or similar?

I understand that the path of the folder_tree could contain the names fully like Top.Science.etc but in this case, the folder names contain spaces and symbols which aren't allowed in LTREE.

This is a very bad design to begin with and I encourage you to refactor it before you continue with the app development. It has a lot of **redundancy** and redundancy is error prone and difficult to modify; for example, if you wanted to move folder "3" somewhere else, you'll need to update MANY rows of data, not a single one, with all risks associated with it. — The Impaler, Apr 28 '20 at 12:29
@TheImpaler How could this be improved? I'm following almost the exact example from the Postgres documentation with LTREE - https://www.postgresql.org/docs/9.1/ltree.html (except I have abstracted the names out to a separate table) — jsrgnt, Apr 28 '20 at 12:32
@jsrgnt: unrelated to your problem, just a tip for the future: you should avoid reading the manual for outdated Postgres version (9.1). e.g. by bookmarking a link to the "current" version — , Apr 28 '20 at 13:07
@a_horse_with_no_name thanks for the heads up! I hadn't realised — jsrgnt, Apr 28 '20 at 13:12

score -1 · Answer 1 · answered Apr 28 '20 at 12:37

This is not an answer but a comment that does not fit in the comments section

The PostgreSQL "ltree" is designed for fast retrieval and search. It's not a typical solution for a relational database model that needs to be modified atomically, lock-free, and avoiding concurrency problems while modifying.

If this was a NoSQL solution it would be OK. However, in the relational modeling you would avoid redundancy. For example your two tables can be modeled into a single one:

+----+--------------+--------+
| id |     name     | parent |
+----+--------------+--------+
|  1 | Top          |   null |
|  2 | Science      |      1 |
|  3 | Astronomy    |      2 |
|  5 | Astrophysics |      3 |
|  6 | Cosmology    |      4 |
+----+--------------+--------+

Postgres recursive LTREE lookup

1 Answers1