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
1
vote
1 answer

What is the difference between LTree and LTree_Gist in Postgresql?

In create table wizard of PGAdmin 4 when you open the column type drop-down menu, there is a type named ltree_gist. Knowing that GIST is probably the best index option to use upon ltree columns, I suspect that ltree_gist is just ltree with an index…
Iman Nia
  • 2,255
  • 2
  • 15
  • 35
1
vote
1 answer

Sort LTree by Upvotes

I am writing a comment system. I have been able to get the hierarchy following the tutorial here (http://www.postgresonline.com/journal/archives/173-Using-LTree-to-Represent-and-Query-Hierarchy-and-Tree-Structures.html) SELECT n.node_path …
nkllz
  • 13
  • 2
1
vote
1 answer

How do I change the path delimiter for ltree paths in PostgreSQL?

The path delimiter for ltree in PostgreSQL is '.', so paths look like root.child.grand-child.* in the queries. But I want to use it to store paths of CRN format, where delimiter is ':', or some other delimiter for that matter. Is there a way to do…
Krishna Birla
  • 332
  • 2
  • 10
1
vote
1 answer

Why does string concatenation not work when querying LTREE in Postgres?

The following SQL works perfect: SELECT node_path, commenttext FROM comments WHERE node_path ~ '*.5f985c80_5205_48cd_b198_1734e0a981d4.*'; But the following gives me an error: SELECT node_path, commenttext FROM comments WHERE node_path ~…
sudoExclaimationExclaimation
  • 7,992
  • 10
  • 47
  • 105
1
vote
1 answer

PostgreSQL: Fast check whether all elements of an LTREE [] <@ LTREE[]

I have the following tables (simplified): CREATE TABLE groups ( id PRIMARY KEY, path ltree, ... ); CREATE TABLE items ( id bigserial, path ltree, ... PRIMARY KEY (id, path) ); For each item, there is a list of groups that…
donkey
  • 23
  • 4
1
vote
1 answer

Does using postgres datatypes like ltree, json is related to NoSQL?

While I am designing a table in PostgreSQL, I planned to use ltree or path datatypes to achieve my functionality. But team members are told if we use ltree then our whole table design will become NoSQL format. Is it true? If Yes, then how I can…
Satz
  • 307
  • 3
  • 19
1
vote
3 answers

Getting error when mapping PostgreSQL LTREE column in hibernate

I am trying to map postgresql ltree column in hibernate as follows: In entity : private String path; @Column(name="org_path", columnDefinition="ltree") public String getPath() { return path; Table structure: CREATE TABLE relationship ( …
Venky
  • 101
  • 1
  • 3
  • 14
1
vote
1 answer

Return only substructure using ltree Postgres plugin

I have the following query and I'm using the postgres plugin ltree. I am trying to do something conceptually similar to cutting the tree along what you could imagine is the y-axis of the tree. I can easily do this with the following query:…
JZ.
  • 21,147
  • 32
  • 115
  • 192
1
vote
1 answer

Query to group by maximum depth of PostgreSQL Ltree?

I want to query for all products with the name "Shania Twain", but I want group them only by the tree with the deepest nlevel. Assuming I have a table like the following with the name categories +---------------+---------------+---------------+ |id …
Mahmoud Abdelkader
  • 23,011
  • 5
  • 41
  • 54
1
vote
0 answers

postgresql nested select with map

I have the following structure: CREATE TABLE folder ( id integer primary key, name varchar(255) ); CREATE TABLE folder_path ( folder_id integer references folder(id), path ltree ); INSERT INTO folder (id,name) VALUES (1,'f1'); INSERT INTO…
Aleksandr Batsuev
  • 161
  • 1
  • 10
1
vote
1 answer

Using ltree query in plpgsql function as param in PostgreSQL

I want to execute query with ltree param in plpgsql function... but i can`t understand how to use quotes in this functions... CREATE OR REPLACE FUNCTION f_select(BIGINT) RETURNS setof categories AS $$ DECLARE s_cat ALIAS FOR $1; queryText…
dezconnect
  • 13
  • 4
0
votes
1 answer

Postgres LTREE: How to do recursive for only a few specific rows?

My actual use case is similar to how comment threads work on Reddit for example. If I do the query based on the LTREE path for the post itself, then it works fine and brings all the child comments. But if I do the query on permalink of a nested…
sudoExclaimationExclaimation
  • 7,992
  • 10
  • 47
  • 105
0
votes
1 answer

In Postgres, when using LTREE, can the ID column be gotten rid of since the `LTREE` path has the ID already as the last label?

Let's say I have a ID TEXT PRIMARY KEY column and this ID is used to create the PATH LTREE column for the row when inserting data. So the ID ends up as the last label in the path. So, is it a wise idea to get rid of the ID column all-together since…
sudoExclaimationExclaimation
  • 7,992
  • 10
  • 47
  • 105
0
votes
0 answers

Postgresql Ltree performance much worse with GisT index

I have this table in a Postgresql database: CREATE TABLE games ( uuid UUID PRIMARY KEY, -- ... more data pgn ltree ); CREATE INDEX pgn_gist_index on games USING GIST (pgn); CREATE INDEX pgn_index ON games USING BTREE (pgn); The table…
MyUsername112358
  • 1,320
  • 14
  • 39
0
votes
2 answers

Validating LTREE hierarchies in PostgreSQL

I'm new to hierarchies in general and LTREE in particular. As I've been converting and loading a column of text-based hierarchies into an LTREE column, I noticed a poorly-formatted string. create table test_tree(id int, path ltree); insert into…
Geolographist
  • 25
  • 6
1 2 3
4
5 6 7