PostgreSQL module ltree

April 2, 2015 | Posted in PostgreSQL

This is a work (post) in progres.....

Dealing with hierarchies is a really important skill. They crop up everywhere. There are many ways to represent trees in databases. There is the adjacency model, the materialized path and nested sets. I have used the adjacency model quite a lot before, but they are not good if your tree is huge. A single query won't be enough to construct a whole tree. Recently I had to query from a table that uses Ltree.

ltree is a sort of materialized path. It is a data type for your column that will have a materialized path for each element in your tree. Consider a hierarchy of geographical places. A place can be child of another place.

  • Continent A
    • Country A
      • State A
      • State B
    • Country B
  • Continent B

We now assign them unique IDs and a materialized parent_path:

  • Continent A ID: 1 parent_id: null parent_path: root

  • Country A ID: 2 parent_id: 1 parent_path: root.1

  • State A ID: 3 parent_id: 2 parent_path: root.1

  • State B ID: 4 parent_id: null parent_path: root

  • Country B ID: 5 parent_id: 1 parent_path: root

  • Continent B ID: 6 parent_id: null parent_path: root

Related Posts

27 Feb 2017 » Provisioning an ubuntu server for Django, Postgres, NGINX

11 Jul 2016 » Installing PostgreSQL

20 Apr 2015 » Postgres backup and recovery

04 Sep 2014 » Forwarding port to a PostgreSQL server