From the course: Advanced SQL for Data Scientists

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Hierarchical data and ltrees

Hierarchical data and ltrees

From the course: Advanced SQL for Data Scientists

Start my 1-month free trial

Hierarchical data and ltrees

“

- [Instructor] Occasionally, we need to work with hierarchical data. There are different ways of working with hierarchical data and one is to use an extension in Postgres called ltrees. So, here's an example of a simple tree and oftentimes one of the things we need to do with these trees is work with paths. So for example, if I were to start at the root and go from node A, which is the root, to node B, and then to node D, and then to node H, we'd have a path from the root to one of the leaves. And we can depict that as a string of characters, such as A.B.D.H. Now, we can model this in different ways. A common way to do this is to use a table that has one row per node. So for example, in this table, node A is the root, so it's parent node is null. A is the parent of B and C, so what we see in the parent node column is the foreign key or the ID of the parent. So in the case of B and C, that would be ID one because node A…

Contents