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
3
votes
2 answers

How install a extension in postgresql before creating the models in django?

Using django 1.8 + Postgres 9+, I have models with custom PG datatypes (like ltree). Creating de database from zero fail because CREATE EXTENSION ltree; is not executed. I try with a empty migration, but that run after the models creation. Exist a…
mamcx
  • 15,916
  • 26
  • 101
  • 189
3
votes
1 answer

How to join a Postgres ltree to a table of labels?

What is the recommended way of using a Postgres ltree common to store product categories? For example, my column could contain an ltree path such as "1.2.3" where 1, 2, and 3 are foreign keys into a table of category labels which can be displayed to…
Jake
  • 15,007
  • 22
  • 70
  • 86
3
votes
1 answer

valid characters in Postgres Ltree label in utf8 charset

the doc of Postgres Ltree said that A label is a sequence of alphanumeric characters and underscores (for example, in C locale the characters A-Za-z0-9_ are allowed). Labels must be less than 256 bytes long. However, it does not said that if we…
andy
  • 3,951
  • 9
  • 29
  • 40
3
votes
1 answer

Best PostgreSQL hiearchical tree for both performance and moving nodes from GUI?

Since I'm using PostgreSQL there is a module which is called ltree, which satisfies at least one of my needs, performance (I don't know about scalability? Someone says materialized path trees does not scale well..). Since the application I'm…
Dac0d3r
  • 2,176
  • 6
  • 40
  • 76
2
votes
1 answer

postgres hierarchy - fill in missing values with ancestor's value

I have a postgres table containing a hierarchy (ltree): | hierarchy | |-----------| | A | | A.X | | A.X.Y. | | A.X.Y.Z | | A.B | | A.B.C | | A.B.C.D | | A.B.C.D.E | and a table of results: | marker | tier | hierarchy…
rjo
  • 25
  • 3
2
votes
1 answer

PostgreSQL ltree - How to get left/right most path and children from a Binary tree constructed with ltree?

I am using PostgreSQL and ltree for constructing a large set of binary tree data. For a particular logic I have to get the left/right most path of a given node. Sample of my Binary tree Sample of my table content Sample input and expected…
Anoop
  • 81
  • 5
  • 14
2
votes
0 answers

When querying over many lquery, should I query an array of lqueries or join them using '|'?

When using the ltree PostgreSQL extension, there are these two operators (among others) at our disposal to compare ltree columns to lquerys. ltree ~ lquery ltree ? lquery[] It seems that you should use ~ for single queries and ? for multiple…
Magmagan
  • 168
  • 2
  • 7
2
votes
2 answers

Spring JPA with Postgres ltree extension (~ '*.)

Not working while getting Message = could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract pathToSearch = "a.b" @Query(value = "SELECT * FROM my_class WHERE path ~…
Ramanuj
  • 123
  • 1
  • 13
2
votes
1 answer

Postgres ltree has-a-child query

I have the following tables: CREATE TABLE public.permissions ( "user" varchar , "action" permissions_action_enum, -- 'read'/'write' id ltree ); CREATE TABLE public.items ( "path" ltree NULL, id uuid NOT NULL, ); I want to…
Michael
  • 896
  • 10
  • 28
2
votes
0 answers

Get nodes between two paths using ltree in PostgreSQL

I'm using PostgreSQL with ltree extension. Consider I have the following structure in my database: Given C node (path: A.C) as a start node and I node (path: A.C.F.G.I) as an end node I want to select all nodes between them including the…
Slava Fomin II
  • 26,865
  • 29
  • 124
  • 202
2
votes
1 answer

Postgresql Ltree query to get comment threads nested json/array and build HTML from it

I am building something which has a comment section similar to Reddit. So there can be root comments and users can reply to the root comments, then users can reply to the child comments and so on. Basically nested comment threads. I used this…
sudoExclaimationExclaimation
  • 7,992
  • 10
  • 47
  • 105
2
votes
1 answer

How to select from an ltree with format n.n.n.n and sort as numbers at each level?

The default sort on the ltree column is as text. Example: I have 3 columns in my table id, parentid and wbs. The ltree column - wbs has 1.1.12, 1.1.1, 1.1.2 stored in different rows. The select query ordered by wbs column returns 1.1.1, 1.1.12,…
Santhosh
  • 23
  • 3
2
votes
0 answers

PostgreSQL Ltree to JSON

I've been experimenting with PostgreSQL's Ltree extension. I'd like to use it to store hierarchical to-do list data (i.e. lists with sub lists). It works well, but after spending a fair bit of time, I still can't find a nice way to retrieve the…
Ishmael7
  • 172
  • 1
  • 10
2
votes
3 answers

Using primary key & foreign key to build ltree

I am pretty new to postgres & especially new to ltree. Searching the web for ltree brought me to examples where the tree was build by chaining characters. But I want to use the primary key & foreign key. Therefore I build the following table: create…
F16R
  • 45
  • 8
2
votes
1 answer

Undefined function: 7 ERROR: operator does not exist: public.ltree public.< public.ltree[]

I have a problem in PHP when I use ltree from PostgreSQL. I do this in SQL: SELECT * FROM tabla t WHERE t.parent_path <@ ( select array_agg(t1.parent_path) from tabla t1 where t1.id in (1000035, 1000045, 1000055, 1000065) ) I run the…
Adrián Fernández Martínez
  • 94
  • 7
1
2
3 4 5 6 7