Questions tagged [tree]
Trees are special cases of graphs. They are acyclic i.e. there is at most one path from any node to any other node. Typical uses include hierarchical data (parent-child relationships; roll-up aggregate values) and BTree indexes.
69 questions
0
votes
0
answers
30
views
Can I use r-tree indexes to find MAX(), MIN() values in arbitrary intervals quickly?
As far I can see, r-tree indexes are used today mostly for geographic systems, for spatial searches.
I need some very similar, but not in space, but in 1-D time series.
Imagine a huge time series, for ...
- 2,137
4
votes
2
answers
118
views
Using bit strings for modeling stable hierarchies in tables, is the faster solution?
Purely relational databases (SQL) seem to suffer to nowadays from the lack of a good solution for search/indexing hierarchies. Here a systematic review of the subject: https://stackoverflow.com/q/...
- 476
2
votes
0
answers
77
views
How to prevent anomalies in a tree data structure
I have two tables node and node_tree:
CREATE TABLE node (
id integer PRIMARY KEY
);
CREATE TABLE node_tree (
node_id integer references node(id) UNIQUE,
parent_id integer references node(id)
);
...
- 179
1
vote
2
answers
92
views
How to convert a recursive query to a function?
I have written a PostgreSQL query that's working perfectly:
WITH RECURSIVE x AS (
SELECT i, parent, id, name, type, '' AS path
FROM entry
WHERE name = 'JS-VBNET-2'
UNION ALL
SELECT e.i,...
0
votes
1
answer
36
views
How to fuzzy query a directory structure in PostgreSQL?
I was able to put together a rough idea for a PostgreSQL query to query over a node table, which contains id, parent__id, slug, and file_url (optional). It's considered a file if it has file_url, ...
- 221
0
votes
0
answers
98
views
Modeling hierarchical data for multiple entities, inherited properties, and varying hierarchy
I am trying to model hierarchical data using a RDBMS but can't quite find the right solution. We are capturing information about storage levels in a warehouse system. There are 8 possible storage ...
- 1
1
vote
1
answer
532
views
SQL query classifying types of nodes in a binary tree
I am using MySQL in trying to solve a problem relating to a SQL query classifying types of nodes in a binary tree.
Specifically, the question states as follows:
You are given a table, BST, containing ...
- 111
2
votes
1
answer
159
views
Joining tables using the longest matching ltree path
Given a table like so:
path (ltree)
a.b.c
a.b
a
d.e
f
How would I write a query to return the longest matching ltree path given an input?
For example:
(input) => expected output
(a.b.c) => a.b.c
...
- 213
2
votes
0
answers
88
views
How to swap the paths of two records (and their children) using a postgresql ltree?
How do I swap two record's paths/positions in an ltree using postgres, where all children follow? There is a uniqueness constraint on the path.
Given the following records on a pages table:
id: 1, ...
- 121
0
votes
0
answers
50
views
Help with design using ltree on PostgreSQL
Currently designing a database to register animal species (Sharks and relatives)
1 table will hold species (name, attributes, and relative information)
I will have the taxonomy hierarchy for each ...
- 101
-2
votes
1
answer
247
views
I want to make a tree of parents only in sql server
My table in the database
How can I get my result as Where child = 'chl1'?
2
votes
0
answers
191
views
DB design for tree structure of user access permissions
I am trying to come up with a good database design for the following problem:
We have an application where users can create objects (such as notes) and link those objects to other objects, projects or ...
- 21
1
vote
0
answers
69
views
TSQL - retrieve all IDs in a tree for a given subnode
I've tried to translate and implement the PostGres solution to the problem found here: PostgreSQL - retrieve all IDs in a tree for a given subnode, but I'm either programming it wrong, or ...
- 111
0
votes
1
answer
516
views
how to update the tree path of the table in PostgreSQL
I have a menu table in PostgreSQL 13 like this:
-- Drop table
-- DROP TABLE public.menu_resource;
CREATE TABLE public.menu_resource (
id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
"...
- 937
-2
votes
1
answer
44
views
Seek query to change a text parent-child relationship into an integer parent-child relationship
What is the query against table a that returns the result below?
with a as ( values
(1,'a023C000002yyrBQAQ',''),
(2,'a023C000002yy0SQAQ','a023C000002yyrBQAQ'),
(3,'a023C000002yy0WQAQ','...
- 31