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
2
votes
1 answer

Postgres - casting text to ltree

Im new to postgres and am looking to utilize ltree for a hierarchal data structure. I have the both the data and the ltree structure (ie domain.class.sublass) in varchar columns, and need to cast the ltree structure into another column with the…
user17104
  • 93
  • 1
  • 6
2
votes
1 answer

PostgreSQL OK to use HASH exclude constraint for uniqueness?

Since hashes are smaller than lengthy text, it seems to me that they could be preferred to b-trees for ensuring column uniqueness. For the sole purpose of ensuring uniqueness, is there any reason the following isn't OK in PG 10? CREATE TABLE test ( …
IamIC
  • 17,747
  • 20
  • 91
  • 154
2
votes
1 answer

Update field with custom types in JOOQ/PostgreSQL

There is a table in PostgreSQL with ltree field. How to update this field with string value? DSL.using(configuration) .update(AREAS) .set(AREAS.TREE, area.getTree());//getTree() return String I tried several…
Alina Didenko
  • 154
  • 1
  • 10
2
votes
2 answers

Syntax error in query using ltree on Postgresql 9.6.5

I have a problem with ltree extension on Postgresql 9.6.5 I have a table called category with the following DDL (I simplified it a bit): CREATE TABLE dictionary.category ( id serial not null constraint category_pkey primary key, name text…
JohnGray
  • 656
  • 1
  • 10
  • 27
2
votes
2 answers

Sequelize support for ~ operator with postgres ltree queries

I have a simple postgres ltree query select * from data_objects WHERE path ~ 'root.*{1}'; I would like to implement this in sequelize, but there is no mapping for the ~ operator. Is it possible to override the mappings? or do I have to use a raw…
Click Ahead
  • 2,782
  • 6
  • 35
  • 60
2
votes
2 answers

JPA Postgres query ltree path

Working with Spring Boot and postgres. I have hierarchical data in the database, with the path stored in an ltree column. I'm trying to grab a particular object based on the path, but am having trouble querying the database. model…
user3051261
  • 343
  • 1
  • 4
  • 9
2
votes
1 answer

sum of ltree children per level

I have a properties table with an ltree path and name columns. Ltree path contains ids of parent nodes, i.e. "7968.7969.7987.8000". Each tree node have reports with some numeric value. I need to find sum of children nodes values for each node in…
Oleg_V
  • 23
  • 4
2
votes
2 answers

Postgres ltree multiple paths

Okay, so I have a table with ltree on a column called path. I want to select multiple paths, but I don't want to have a ton of OR statements. Is that possible or is this the best way to do it? Paths: 'schools.myschool.*'…
Silent Knight
  • 21
  • 4
2
votes
1 answer

Postgresql ltree query to find parent with most children; excluding root

I am using PostgreSQL and have a table with a path column that is of type ltree. The problem I am trying to solve is: given the whole tree structure, what parent has the most children excluding the root. Sample data looks like this: path column = ;…
Davinj
  • 327
  • 4
  • 16
1
vote
1 answer

how to get last known contiguous value in postgres ltree field?

I have a child table called wbs_numbers. the primary key id is a ltree A typical example is id series_id abc.xyz.00001 1 abc.xyz.00002 1 abc.xyz.00003 1 abc.xyz.00101 1 so the parent table called series. it has a field called…
Kim Stacks
  • 10,202
  • 35
  • 151
  • 282
1
vote
0 answers

PostgresSQL ltree - getting all the ancestors and all the direct children of those ancestors

I have a simple tree structure in a PostgreSQL database, The tree table structure looks like this : | uid (oid) | parent (oid) | path (ltree) | label (string) | | -------- | ------------ | ----------- | ------------- | | 1 | null …
Sunbird
  • 11
  • 4
1
vote
1 answer

How to filter out with lquery?

I'm using LQUERY to do some custom filtering in queries from my PostgreSQL -database. One of the fields is a ltree -field called path and I should be able to check which objects have a certain string in their path and filter them out. I've tried…
lr_optim
  • 299
  • 1
  • 10
  • 30
1
vote
1 answer

Fetch the unique rows from postgres database as per longest unique path in ltree

I am working over postgres in db we have below table where we have PATH column as ltree for storing hierarchy ids. Table (Location)- | ID(PK) | User |PATH (parent hierarchy) | |--------|----------|---------| | 1 | Parent | null | | …
Nitesh Sharma
  • 545
  • 3
  • 14
1
vote
0 answers

Problem with optimizing an lpath-dependent query

Application I am working on has a feature called folders. Folders may contain other folders, are owned by users and can be shared to other users. The feature has been implemented and was working fine up until recently, when a particular API endpoint…
oldhomemovie
  • 14,621
  • 13
  • 64
  • 99
1
vote
1 answer

Why in LTree example in Postgresql documentation, the author has created two indexes over the ltree column?

Can someone please explain why two indexes mechanism (a btree and a gist) are defined in an example given by Postgresql documentation (Please Check F.21.4. Example section). This is the example code : CREATE TABLE test (path ltree); INSERT INTO…
Iman Nia
  • 2,255
  • 2
  • 15
  • 35
1 2
3
4 5 6 7