Data typesversion 1.3

ltree

Tree paths such as categories and org charts.

What it is for

ltree stores a position in a tree as a dotted path (shop.clothing.shirts) and indexes it, so "everything under this node" and "all ancestors of this node" are single indexed queries instead of recursive CTEs.

Enable it

sql

create extension if not exists ltree with schema extensions;

Example

sql

create table categories (id serial primary key, path ltree not null);
create index on categories using gist (path);

insert into categories (path) values ('shop'), ('shop.clothing'), ('shop.clothing.shirts');

-- the subtree under shop.clothing
select path from categories where path <@ 'shop.clothing';

Questions

How do I enable ltree?

Run create extension if not exists ltree with schema extensions; in the SQL editor, or switch it on from the Extensions page in the console. You do not need superuser access.

Which version of ltree is installed?

1.3, on Postgres 17.6, as read from the running engine on 2026-10-08. Check yours with: select extversion from pg_extension where extname = 'ltree';

Upstream project: www.postgresql.org/docs/17/ltree.html

More data types extensions

uuid-ossp UUID generators (v1 to v5).
pg_uuidv7 Time-ordered UUID v7 values.
citext Case-insensitive text, for emails and user names.
hstore Key-value pairs in one column.
intarray Functions and indexes for integer arrays.
cube Multi-dimensional points and boxes.

Try ltree on Scribase All extensions