9 votes
Accepted

PostgreSQL - retrieve all IDs in a tree for a given subnode

A very primitive implementation: It basically divides the problem into two subproblems: First find all the ancestors of the node in question (including the node itself). If the node has no parents, ...
ypercubeᵀᴹ's user avatar
6 votes
Accepted

How to turn a set of flat trees into a single tree with multiple leaves?

A recursive CTE (rCTE) is the right tool to follow the path and identify all nodes of a tree. But it does not allow aggregation in the recursive term. There is an elegant alternative: Recursive ...
Erwin Brandstetter's user avatar
5 votes

PostgreSQL - retrieve all IDs in a tree for a given subnode

This function returns the parent level of node_id: There is a 'level' row due there isn't a row (id, null) for parent row. CREATE FUNCTION get_parent(node_id int) RETURNS integer AS $$ WITH ...
McNets's user avatar
  • 23.8k
5 votes

Is it possible to expose the max depth of the B-Tree behind a rowstore index or a way to see how many nodes were traversed for a particular query?

Some of this information like index depth can be found in DMF dm_db_index_physical_stats(). Some outer interesting information that can be found in it is number of used data pages and the ...
Peter Å's user avatar
  • 345
5 votes
Accepted

Exclusion constraint on recursive foreign key (on the example of a Tree)

Use a composite foreign key: (tree_id, parent_id) REFERENCES (tree_id, id) You will need to first change the PK or add a UNIQUE constraint on (tree_id, id)
ypercubeᵀᴹ's user avatar
3 votes

Joining two ltree based tree tables recursively

Essentially, what you're saying is that a role of x.y.z, has all of the permissions granted to parent x.y, and grandparent x a permission of x.y.z, has all of the permissions granted to parent x.y, ...
Evan Carroll's user avatar
  • 63.2k
3 votes

Joining two ltree based tree tables recursively

Inner join returns all ancestors of an specific path, I've used array_agg(distinc to avoid duplicates. select array_agg(distinct p2.name) permissions from ...
McNets's user avatar
  • 23.8k
3 votes
Accepted

Create JSON object from recursive tree structure

A recursive CTE (rCTE) does not allow aggregation in the recursive term. So there is no simple solution. I suggest a recursive function for an elegant solution: CREATE OR REPLACE FUNCTION f_item_tree(...
Erwin Brandstetter's user avatar
3 votes

COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

You should use LEFT JOIN instead of INNER JOIN in case category doesn't have subcategory. SELECT `parent`.`id` , `parent`.`name` , COUNT(`child`.`id`) AS `hasChild` ...
Mr. K's user avatar
  • 31
2 votes

DB schema for storing the process tree of linux

The design should be according to your requirements. If your requirement includes to store only some columnar data that are mostly required (mostly not null) then use tables with relations (foreign ...
Sahap Asci's user avatar
  • 2,919
2 votes
Accepted

Binary tree "get open list" SQL query

In this case I think you don't need a recursive query, simply get those records that do not have two child records. select * from mytable where id not in(select parent_id from ...
McNets's user avatar
  • 23.8k
2 votes
Accepted

Representing ownership of heirarchical tree structure

I've thought of several possible solutions, but they all leave something to be desired. For example, I could add an owner_id column to the folders table, referencing users (id). The negative is that ...
Evan Carroll's user avatar
  • 63.2k
2 votes
Accepted

How to limit tree traversal in case of loops?

It's pretty simple, really: you add the recursion level value and filter on it: WITH RECURSIVE t(item_id, json, level) AS ( SELECT item_id, to_jsonb(items), 1 FROM items WHERE ...
mustaccio's user avatar
  • 26k
2 votes

How to limit tree traversal in case of loops?

I'm not entirely clear from your question whether you primarily want to just limit depth traversal, or if in reality you would actually want a solution for recursion loops if it was possible. I've ...
Charlieface's user avatar
2 votes
Accepted

handling primary key with large tree, best way to get root id from child table X

From a very basic overview, I don't think your existing design is bad. In general, it's good to start out following a somewhat normalized pattern with your table design. Usually, I'll try to keep my ...
J.D.'s user avatar
  • 37.5k
2 votes
Accepted

Recursively find the path to all leaves descending from a given parent in a tree

WITH RECURSIVE cte AS ( SELECT id, parent_id, id::TEXT path, NOT EXISTS ( SELECT NULL FROM graph gr ...
Akina's user avatar
  • 19.9k
2 votes

Traversing tree-like data in a relational database using SQL

The standard SQL method is a “Recursive query” which is provided by the Recursive CTE and designated as WITH [ RECURSIVE ] in the query. The implementation isn't specified in the spec, only the ...
Evan Carroll's user avatar
  • 63.2k
1 vote
Accepted

how to update the tree path of the table in PostgreSQL

Here is one example, you probably need to adjust it slightly: create table tmp (id int, parent_id int, tree_path_id text); insert into tmp (id, parent_id, tree_path_id) with recursive t (id, ...
Lennart - Slava Ukraini's user avatar
1 vote
Accepted

Sql, get category tree for values from one table, names and ids

Example. Structure and data: CREATE TABLE tree (id INT, parent INT, name VARCHAR); INSERT INTO tree VALUES (1,0,'root'), (2,1,'branch 1'), (3,1,'branch 2'), (4,2,'branch 3'), (5,2,'branch 4'), (6,4,...
Akina's user avatar
  • 19.9k
1 vote

PostgreSQL, Stored Procedures, Recursive call Tree structure

When 4th child appears it should be added to first parent's child as a child. I suspect that new child should be added to the nearest parent's child having less than 3 childs. So the problem is ...
Kondybas's user avatar
  • 4,348
1 vote
Accepted

How to Store Trees on Disk

Every XML database will have its own solutions to these problems. You could do worse that explore the source code of an open-source XML database. The main aspects of any solution will be (a) the ...
user1359's user avatar
  • 171
1 vote

Bypass truncated data type error in `CREATE TABLE AS`

After much googling, I came to realize that the error message was exactly correct though somewhat vague. Truncated incorrect DOUBLE value: '1\x00\x00\x00' meant that somewhere in my query, my CAST ...
Noah Goodrich's user avatar
1 vote
Accepted

How can I get performance using PostgreSQL CTE recursive?

I found the solution. I was using the recursive expression how parameter to join and it was do several lops on the table used on join, a better aprouch is before join with this table(sadt), do the ...
Tiago Mendes's user avatar
1 vote

Create JSON object from recursive tree structure

Here is an example query, WITH RECURSIVE t(item_id, json) AS ( SELECT item_id, to_jsonb(items) FROM items WHERE NOT EXISTS ( SELECT 1 FROM joins ...
Evan Carroll's user avatar
  • 63.2k
1 vote

Representing ownership of heirarchical tree structure

A possibility is to split the folder table in two, treating the root folders as they were special case of folders (inheritance): CREATE TABLE users ( id SERIAL PRIMARY KEY, name TEXT NOT NULL ...
Renzo's user avatar
  • 4,080
1 vote

What would be the maximum number of records that can be indexed with a three levels B-tree ? B+ tree?

There are many implementation options available to developers of BTree and B+Tree algorithms that will affect the answer here. In a simplistic BTree, all nodes are the same size, and when a node ...
cliffordheath's user avatar
1 vote

How to create data tree in MS Access

I believe you are asking about a table design or schema. Whether you use 1 or many tables is up to you. The increased complexity of normalisation is a trade off between efficiency and convenience. ...
Sir Swears-a-lot's user avatar
1 vote

How to best store a directory tree in a database?

I'm a DB noob as well, I do recall postresql having json datatype for storing JSON structures. Maybe you can review the postgresql doc and decide if it works for you.
pvod's user avatar
  • 21
1 vote

Can InnoDB use a clustered index as a covering index?

How indexes work in InnoDB. This discussion is limited to InnoDB. (Let me establish some ground work, then I will get to your question.) The PRIMARY KEY is a BTree where the leaf nodes contain all ...
Rick James's user avatar
  • 78.1k
1 vote

B Tree and B+Tree differences

In the original description of a B-tree the internal nodes held not just the index keys but all columns of the table. In the B+-tree only the leaf nodes store all columns' values. As each node in the ...
Michael Green's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible