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

PostgreSQL ltree: Get all entries and the ancestors in ltree from another table

Tree table node_Id tree_path::ltree aaaa aaaa bbbb aaaa . bbbb cccc aaaa . bbbb . cccc dddd aaaa . bbbb . dddd eeee eeee ffff eeee . ffff Object…
asky
  • 1
  • 1
0
votes
2 answers

POSTGRES : ltree ancestors is not ordered

I am looking for information but I can't find it in the documentation I execute a request on my path to ask for the ancestors, I receive all the ancestors but they are all mixed, do you have an idea of my error? Is it possible to have the order…
Pascal de Sélys
  • 127
  • 1
  • 10
0
votes
0 answers

Postgres ltree : How to limit the max number of children at any given level?

I'm working on a reddit-like application that has comment trees, and am in the process of adding tree-paging: IE instead of returning the entire tree, I need to limit the number of results returned. For example: The top level might have 15k…
dessalines
  • 6,352
  • 5
  • 42
  • 59
0
votes
0 answers

postgres cast to type from other schema extension

The situation: I have a database with multiple schemas. I've installed the ltree extension in public as such: CREATE EXTENSION IF NOT EXISTS ltree schema public; and then created the following function CREATE or replace FUNCTION…
Stefanvds
  • 5,868
  • 5
  • 48
  • 72
0
votes
0 answers

postgres ltree gist index kept growing in size

I have a gist index for a ltree field, declared like below: path | ltree | | | I realized this index grew quickly to 185GB, even though I didn't insert crazy amount of content. Note the size and…
Behlül
  • 3,412
  • 2
  • 29
  • 46
0
votes
1 answer

How to select empty paths with POSTGRES LTREE

I have a simple table defined in Postgres, where I'm using LTREE, but some rows can have empty paths: CREATE TABLE films ( id serial PRIMARY KEY, title varchar(40) NOT NULL, path ltree DEFAULT NULL ); If I insert the following…
Jani
  • 507
  • 3
  • 21
0
votes
1 answer

Get all ltree nodes at depth

I have an ltree column containing a tree with a depth of 3. I'm trying to write a query that can select all children at a specific depth (level 1 = get all parents, 2 = get all children, 3 = get all grandchildren). I know this is pretty…
Optimum
  • 146
  • 2
  • 11
0
votes
2 answers

Postgres ltree doesn't use Gist index Why?

CREATE TABLE hierarchy_table(id integer PRIMARY KEY,path ltree); INSERT INTO hierarchy_table VALUES (1, '1'), (2,'1.2'), (3,'1.2.3'), (4,'1.2.4'), (5,'1.5'), (6,'1.5.6'), (7,'1.5.7'), (8,'1.5.8'); CREATE INDEX idx_hierarchy_table_gist ON…
László Tóth
  • 483
  • 5
  • 15
0
votes
1 answer

sqlalchemy / ltree update request result different than the expected one

I have some problem with sqlalchemy during a session.execute query. sqlalchemy version == 1.3.5 sqlalchemy utils version == 0.34.1 postgres version == 10 I implemented a function to update ltree nodes with sqlalchemy, taking…
ggirodda
  • 770
  • 7
  • 19
0
votes
1 answer

Unable to use "gist_ltree_ops(siglen=100)" for setting signature strength in EF Core code first approach

I am using EF Core code first approach and trying to configure signature strength of the column type ltree using the below snippet. public class Node { public Guid Id { get; set; } public LTree Path { get; set; } public string Name {…
Nithin
  • 45
  • 7
0
votes
1 answer

Postgres ltree with variables in query

I use variable in psql like: \set user_id 10; When i want use this variable in query like select * from users where user_id = :user_id; - it's ok But when i use this for ltree column i have a problem select * from accounts where customer_id <@…
Аушев
  • 35
  • 3
0
votes
1 answer

Get immediate children from Postgres ltree path

How do I get the immediate children from a given path in an ltree type in Postgres assuming the path always starts at root? For example if the given path is A.B and the column name of ltree is path, then the best I could come up with is: SELECT…
Dula
  • 1,404
  • 1
  • 14
  • 29
0
votes
1 answer

How to check if a node is repeated in a ltree - PostgreSQL

How to check if a node is repeated (exits more than one) in a ltree? Something like this: '0.1.2.3.4.3.9' ==> True '0.1.2.3.4.5.9' ==> False
Milad
  • 692
  • 2
  • 9
  • 27
0
votes
1 answer

Can't use ltree data type in postgresql

According to https://dzone.com/articles/installing-the-postgres-ltree-extension, ltree is installed and enabled on my computer. But when I try to make a table with a column of type ltree in Postbird, it says 'type "ltree" does not exist'. This makes…
eric_heim
  • 31
  • 1
  • 6
0
votes
1 answer

Postgres Ltree Extension with native query Java Entity Manager

I'm trying to fetch a tree from postgres DB using a native query, the next query is working fine using psql terminal: SELECT col_1 FROM my_tree WHERE parent_id ~ lquery('*.C.*') but when I add the same query using entity manager: private…
vcg
  • 86
  • 5
1 2 3 4
5
6 7