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

Find full path from list of tree subpaths

Is there a simple way to find the full path and update it for all topic with such data structure. TOPIC_0000070 | TOPIC_0000054 / | \ TOPIC_0000000…
Vitalii Shkurenko
  • 69
  • 1
  • 9
0
votes
0 answers

How to find the average progress (per level) in a hierarchical tasks tree with PostgreSQL?

I´m trying to determine the average progress of each task group in a tasks tree. The problem is that only the leaf nodes (which are in a diferent table ‒ a child table ‒) actually have the progress field. I have a Tasks (parent) table and an…
Alex_89
  • 371
  • 5
  • 15
0
votes
1 answer

Select Rows and have children of said rows as a column using ltree

"Content" table has a column "ltree" - Content items are created with a hierarchy based on this ltree. For example: Title LTREE Record 1 1 Record 2 1.2 Record 3 1.2.3 What I'm attempting to do is create a SELECT statement in which I…
nvancouver
  • 137
  • 11
0
votes
0 answers

Laravel 7 with postgresql ltree does not work

I need to update my application from laravel 5 to laravel laravel 7 I'm using postgresql ltree on few column and this is my migration code DB::statement("ALTER TABLE mytable ADD COLUMN mycolumn ltree"); that migration is running well on laravel 5,…
panda
  • 1
  • 1
0
votes
1 answer

Postgres ltree Query to fetch data after the hierarchy using column name

I wanted to fetch Postgres ltree hierarchy after certain level using subltree and column name. I am able to fetch when i provide the exact input but not able to do so with column name. This is working fine: select * from…
Rahul Anand
  • 165
  • 7
0
votes
0 answers

PostgreSQL: How do I identify the ltree paths first, and then use the identified paths to find their nested ltree paths?

Say I want to look up the ltree paths for "active" recipes, and then use the those paths to find descendants of those recipes. # recipes name | category | path ------------------------- Main active recipeA Sides inactive …
sjsc
  • 4,552
  • 10
  • 39
  • 55
0
votes
2 answers

PostgreSQL ltree find all child node of a given path (With out using expression)

Only child node of given path (Except the given path) Query: select path from tree where path <@ 'a.b.c'; Result: Expected result: All the below node of a.b.c (In result don't needed a.b.c)
Ramanuj
  • 123
  • 1
  • 13
0
votes
1 answer

Postgres recursive LTREE lookup

I have the following two tables that represent a nested folder hierarchy Table "folder" contains basic folder information +----+--------------+ | id | name | +----+--------------+ | 1 | Top | | 2 | Science | | 3 | Astronomy…
jsrgnt
  • 154
  • 5
0
votes
1 answer

Using Sequelize/ORM with Postgres extension types like LTREE and HSTORE

I have an existing app that is using Node.JS & Express & Sequelize & PostgreSQL and now new requirements came up that require using PostgreSQL extension types like ltree to handle tree data model and hstore for diverse key/value attributes on…
Luis Trigueiros
  • 636
  • 7
  • 21
0
votes
1 answer

ltree postgres type using spring data jpa -- geting syntax error after defining a function and cast within postgres

When attempting to write an entity containing a value of ltree type, I was getting the following error: column "path" is of type ltree but expression is of type character varying Googling that error got me to Macaddr/Inet type of postgres in…
smacdav
  • 405
  • 7
  • 16
0
votes
2 answers

LTREE ancestor query in Postgres not working as expected

I have a table with an LTREE column, and the data is somewhat as described below. ID Label Path 1. ABC. 1 2. DEF. 1.2 26. GHI. 1.2.26 Let's suppose I want to find all the ancestors of the node labelled GHI. The query I'm using is SELECT…
Anomitra
  • 1,111
  • 15
  • 31
0
votes
1 answer

Spring JPA with Postgres ltree extension

I am trying to make ltree indexing work with Spring JPA. I have an entity that contains the path like this @Type(type = "com.thomaster.ourcloud.model.filesystem.LTreeType") @Column(columnDefinition="ltree") private String relativePath; The…
Tamás Regőczi
  • 1
  • 6
0
votes
1 answer

hibernate: catch exception while saving invalid ltree column

I have an entity that has a column of type ltree and this type has rather narrow character acceptance so exceptions occur. now I need to be able to catch them. this is how I'm saving the entity: @Component @AllArgsConstructor class…
greengold
  • 1,184
  • 3
  • 18
  • 43
0
votes
1 answer

How to improve my ltree dot notation hierarchy to nested JSON script?

I receive a set of rows from a database with a parentPath column formatted as ltree string (dot-notation) representing a hierarchy structure. I want to return the same data with nested categories from my nodejs server. The data I have : const…
dimitri
  • 348
  • 2
  • 6
0
votes
1 answer

Postgres LTREE display by grouping parents and children

I have a sample Ltree Structure than I want to return it as JSON structure. I've tried searching on stack overflow but the results are giving wrong responses. create table node ( id integer not null, name varchar(255), path ltree …
BardZH
  • 402
  • 2
  • 6
  • 21
1 2 3 4 5
6
7