Extensions
ltree

ltree

ltree : data type for hierarchical tree-like structures

Overview

ID Extension Package Version Category License Language
3960
ltree
ltree
 1.3
TYPE
PostgreSQL
C
Attribute Has Binary Has Library Need Load Has DDL Relocatable Trusted
--s-dt-
No
Yes
No
Yes
no
yes
Relationships
Need By
ltree_plpython3u
See Also
prefix
semver
citext
unit
pgpdf
pglite_fusion
md5hash
asn1oid

Packages

PG18 PG17 PG16 PG15 PG14
1.3
1.3
1.3
1.3
1.3

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

CREATE EXTENSION ltree;

Usage

ltree: hierarchical tree-like label data type

The ltree extension provides data types for hierarchical tree-structured data with extensive search facilities.

CREATE EXTENSION ltree;

Data Types

  • ltree: Label path (e.g., Top.Science.Astronomy)
  • lquery: Regex-like pattern for matching ltree values
  • ltxtquery: Full-text-search-like pattern

Basic Usage 

CREATE TABLE categories (path ltree);
INSERT INTO categories VALUES
    ('Top'), ('Top.Science'), ('Top.Science.Astronomy'),
    ('Top.Hobbies'), ('Top.Collections.Pictures');

-- Find descendants
SELECT path FROM categories WHERE path <@ 'Top.Science';

-- Pattern matching
SELECT path FROM categories WHERE path ~ '*.Astronomy.*';

-- Full-text search
SELECT path FROM categories WHERE path @ 'Science & !Pictures';

Operators

Operator Description
@> Is ancestor of (or equal)
<@ Is descendant of (or equal)
~ Matches lquery pattern
? Matches any lquery in array
@ Matches ltxtquery
|| Concatenate paths

lquery Patterns 

'*.Science.*'           -- any path containing Science
'Top.*{1,2}.Astronomy'  -- 1-2 labels between Top and Astronomy
'*.astro*'              -- prefix matching
'*.Astro*@'             -- case-insensitive prefix

Functions 

SELECT nlevel('Top.Science.Astronomy');                     -- 3
SELECT subltree('Top.Science.Astronomy', 1, 2);            -- Science
SELECT subpath('Top.Science.Astronomy', 1);                 -- Science.Astronomy
SELECT index('a.b.c.d', 'b.c');                             -- 1
SELECT lca('1.2.3', '1.2.3.4.5');                          -- 1.2
SELECT lca(ARRAY['1.2.3'::ltree, '1.2.4'::ltree]);        -- 1.2

Index Support 

-- GiST index (supports @>, <@, ~, ?, @)
CREATE INDEX path_gist_idx ON categories USING gist (path);

-- B-tree index (supports <, <=, =, >=, >)
CREATE INDEX path_idx ON categories USING btree (path);
Last updated on
cubehstore