I want to store hierarchical data in the PostgreSQL database. I found ltree extension but it is used to store tree-structured data i.e there can be only one parent node. Is there any way I can tweak it to store multiple parent nodes?
1 Answers
Yes, it is possible to do so using the ltree extension, by using two tables: one for your nodes and another for your paths. Bustawin has a great writeup on how to work with DAGs using this extension with this strategy.
For a node to have multiple parents, it is necessary that we define more than one ltree path for such node, one that leads from each parent. Say we have a DAG that forms a diamond between nodes A, B, C, and D:
A -> B
B -> D
A -> C
C -> D
Using the extension, you should have a node D related to two paths, A.B.D and A.C.D. Both queries should return node D
SELECT * FROM nodes
JOIN paths ON paths.node_id = nodes.id
WHERE paths.ltree_path ~ "A.B.*{1}"
SELECT * FROM nodes
JOIN paths ON paths.node_id = nodes.id
WHERE paths.ltree_path ~ "A.C.*{1}"
Related