Stack Overflow
Stack Overflow

Questions tagged [ltree]

`ltree` is a PostgreSQL extension for representing labels of data stored in a hierarchical tree-like structure. Extensive facilities for searching through label trees are provided.

The ltree bundled extension for PostgreSQL is a contrib module that ships as part of PostgreSQL. It defines an ltree data type to add functionality for representing labels of data stored in a hierarchical tree-like structure. Extensive facilities for searching through label trees are provided.

ltree is loaded with CREATE EXTENSION. See the documentation linked above for details on usage.

For more information see .

101 questions
13
votes
1 answer

PostgreSQL ltree- vs tree module vs integer/string arrays or string delimited path

As you may know there's a module for PostgreSQL called ltree. Also you have the possibility to use the Array type for integers (*1, see comment below), which in this test shows to actually perform a little slower with its recursive queries, compared…
Dac0d3r
  • 2,176
  • 6
  • 40
  • 76
12
votes
2 answers

PostgreSQL ltree find all ancestors of a given label (not path)

I have a table with an ltree path column. In my hierarchy the path to a label is unique, meaning that every label has exactly one parent label. In other words, there are no two ltree paths in the table which would end with the same label. I have an…
Zoltán Tamási
  • 12,249
  • 8
  • 65
  • 93
10
votes
1 answer

Postgres Materialized Path - What are the benefits of using ltree?

Materialized Path is a method for representing hierarchy in SQL. Each node contains the path itself and all its ancestors (grandparent/parent/self). The django-treebeard implementation of MP (docs): Each step of the path is a fixed length for…
Avi Kaminetzky
  • 1,489
  • 2
  • 19
  • 42
9
votes
2 answers

PostgreSQL Materialized Path / Ltree to hierarchical JSON-object

I have this materialized path tree structure built using PostgreSQL's ltree module. id1 id1.id2 id1.id2.id3 id1.id2.id5 id1.id2.id3.id4 ... etc I can of course easily use ltree to get all nodes from the entire tree or from a specific…
Dac0d3r
  • 2,176
  • 6
  • 40
  • 76
6
votes
5 answers

SQL: How do I update a value inside of an ltree?

As the title suggests, I'm having a hard time, guessing how I can efficiently update a value in multiple rows. The column is implemented with the data-type ltree. So, there should be some form of special operations available for this data type in…
Sazid
  • 2,747
  • 1
  • 22
  • 34
5
votes
3 answers

Is it possible to create a unique constraint that includes the entire path of an ltree field?

I'm using the ltree extension in Postgres to manage paths for a tree. I want to ensure that the name field is unique for any given path on the tree. Can I accomplish this using a constraint or do I have to build this into the query? demo…
Travis
  • 51
  • 1
5
votes
2 answers

Postgres ltree query, count joined items on each level of tree

I have 3 tables: LOCATION, LOCATION DESCRIPTION which holds languages for each location etc and 1 for STORE. LOCATION DESCRIPTION table also holds the hierarchy in an ltree path field like below: city.district1 city.district1.area1 …
mallix
  • 1,399
  • 1
  • 21
  • 44
4
votes
1 answer

Postgres ltree module, update id of a node with all the children related paths

I have the following simple table: create table nodes( id text primary key, path ltree ); lets say I put some data to the table: insert into nodes (id, path) values ('A', 'A'); insert into nodes (id, path) values ('B', 'A.B'); insert into…
Andrey Yaskulsky
  • 2,458
  • 9
  • 39
  • 81
4
votes
2 answers

Java type in JDBC to Postgres ltree

Does anyone know what Java type maps to a Postgres ltree type? I create a table like so: CREATE TABLE foo (text name, path ltree); A couple of inserts: INSERT INTO foo (name, path) VALUES ( 'Alice', 'ROOT.first.parent'); INSERT INTO foo (name,…
Ove Sundberg
  • 333
  • 3
  • 10
3
votes
2 answers

How to filter by path with PostgreSQL

I have some resources in my database that are inherited to their sub-resources. I need to be able to get also the inherited resources when I query a resource. I have a field called path which I'm planning to use. The path always contains the full…
lr_optim
  • 299
  • 1
  • 10
  • 30
3
votes
1 answer

Can we represent DAG(directed acyclic graph) using PostgreSQL ltree?

I want to store hierarchical data in the PostgreSQL database. I found ltree extension but it is used to store tree-structured data i.e there can be only one parent node. Is there any way I can tweak it to store multiple parent nodes?
semicolon
  • 43
  • 5
3
votes
0 answers

How to filter a number of records and get only the outer most records from a postgres ltree structure?

I have database records arranged in an ltree structure (Postgres ltree extension). I want to filter these items down to the outer most ancestors of the current selection. Test cases: [11, 111, 1111, 2, 22, 222, 2221, 2222] => [11,…
Raam
  • 63
  • 1
  • 8
3
votes
1 answer

Knex : nested raw queries, escape the '?' character

I'm trying to use the Postgresql LTREE with knex. To manage it I have to use raw queries of knex because obviously LTREE is not native in knex (it is specific to postgresql) An operator in postgresql and LTREE is the character ?, in knex.raw, the…
Jean-Baptiste Duriez
  • 53
  • 1
  • 6
3
votes
2 answers

Search with multiple parameter in postgresql ltree

I planned to implement a database that using ltree as multiple level categorization. However i ran into trouble when i tried to get an entry with path x or y. new_table +-------+--------+---------+ | id | name | path …
Haidar Aji
  • 33
  • 4
3
votes
2 answers

Collect all leaf nodes with Postgres ltree

I have been using Postgres ltree construct to store a hierarchical structure. Now, I want to collect all the leaf nodes in the tree. Is there a straightforward mechanism to do this? CREATE TABLE foo AS SELECT node::ltree FROM ( VALUES …
Phelodas
  • 3,853
  • 5
  • 25
  • 30
1
2 3 4 5 6 7