Postgres LTREE

In PostgreSQL, the ltree extension allows us with an access to the ltree data type. Using this data type, we can store the labels of data in a hierarchical tree-like structure.

The ltree extension is particularly powerful for tasks that involve complex hierarchical relationships such as a data stored in multi-level categories, filesystem-like paths, or nested sets.

This guide explores how we can enable the ltree extension and use the provided data type.

Enable the LTREE Extension in PostgreSQL

Before we can use the ltree, we need to enable the extension in your database. Connect to your database and run the following command:

CREATE EXTENSION IF NOT EXISTS ltree;

This should enable the extension on the database which provides access to the ltree data type.

Let us now discuss how to work with the ltree type in Postgres.

PostgreSQL LTREE Examples

The following example steps should demonstrate how to configure and use the ltree data type:

Create a Table LTREE Column

Let’s create a simple table that represents a filesystem with the path as a ltree type.

CREATE TABLE files (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
path LTREE
);

Insert the Data

Next, let’s insert some sample data into the table. The ltree values are textual labels which are separated by (dot). Each label can contain alphanumeric characters and underscores and must not exceed to 256 characters.

INSERT INTO files (name, path) VALUES
('root', 'root'),
('etc', 'root.etc'),
('passwd', 'root.etc.passwd'),
('home', 'root.home'),
('user', 'root.home.user'),
('documents', 'root.home.user.documents');

The resulting table is as follows:

Query the Ltree Data

To find all the dependencies of a node, we can use the <@ operator as shown in the following example:

SELECT * FROM files WHERE path <@ 'root.home';

This should return the dependencies of the “root.home” ancestor.

We can also find all ancestors of a node using the @> operator. An example is as follows:

SELECT * FROM files WHERE path @> 'root.home.user';

Output:

This returns all the ancestors of the “root.home.user” including the “root.home.user”, “root.home”, and root.

To find all nodes that match a specific pattern, run the following:

SELECT * FROM files WHERE path ~ 'root.*.user';

Result:

We can also find the depth of nodes using the ndepth function as follows:

SELECT path, nlevel(path) FROM files;

Output:

Update and Delete the Ltree Data

We can update and delete the ltree data just like any regular PostgreSQL data. Example commands are as follows:

-- Update data
UPDATE files SET path = 'root.var' WHERE name = 'etc';

-- Delete data
DELETE FROM files WHERE name = 'passwd';

Index Ltree Data

We can also create indexes for the ltree data using the GiST and SP-GiST index types. For example, to use the gist index, we can run the following query:

CREATE INDEX files_path_gist ON files USING gist(path);

Conclusion

This post explored the fundamentals of enabling the ltree extension in PostgreSQL. We also explored how we can create and use the ltree data types. Feel free to consult the PostgreSQL documentation to explore more on ltree types and features.