Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

All Questions

Tagged with
Filter by
Sorted by
Tagged with
0
votes
0answers
24 views

How to UPSERT tree in PostgreSQL?

There is a tree-structures in two-tables with ParentId field. How to make UPSERT (INSERT ... ON CONFLICT (...) DO UPDATE ...) between them? The problem is that parent record for the current record in ...
0
votes
1answer
46 views

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

I have the table with recursive foreign key. It's just hierarchical tree structure: CREATE TABLE tree ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, value ...
0
votes
1answer
89 views

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

What I need I need select all ids or names from table "category" (all chidrens) until category_id is 0 (main category). Example On web there is product with category tree. In table "category" I have ...
0
votes
1answer
45 views

PostgreSQL, Stored Procedures, Recursive call Tree structure

I'm faced with a problem.I have a ternary structured data. The relation is from child to parent, more specific a child has an parent_id. A parent shouldn't have more than 3 children. When 4th child ...
0
votes
1answer
38 views

Optimizing one-way syncing of large (wide) tree

I deal with several trees that can vary in size (they all have depths of ~8-10) but it's not unusual for them to consist of ~50-100k nodes. At the moment, the trees are stored using simple parent_id ...
0
votes
0answers
18 views

Using LTREE type when the final node has ~1000 descendants, which exist under other final nodes

Is it inappropriate to use LTREE type when the final node has ~1000 descendants, which exist under other final nodes? My data is essentially a directory tree, with the final level containing text ...
1
vote
2answers
154 views

Joining two ltree based tree tables recursively

I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join ...
5
votes
2answers
944 views

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

I have a non-binary tree of customer, and I need to obtain all the IDs in a tree for the given node. The table is very simple, just an join table with a parent id and child id. This is a ...
1
vote
0answers
63 views

Scalable way to manage aggregated statistics over time in hierarchy?

I'm using a closure table (Postgres through Django) to roll up stats through a hierarchical tree. When a node that is 10 layers deep has a value that increases, each parent's sum total of that value ...
0
votes
1answer
567 views

How can I get performance using PostgreSQL CTE recursive?

I did a tree structure using id and parent_id in the same table. For query's I'm using CTE provide by PostgreSQL, but it's spend so much time to do the joins with recursive results. For example, by ...
2
votes
1answer
1k views

Recursive self relational via join column

Having this specific simple many to many self referential structure An item owns other items through the joins table; trying to retrieve the whole tree structure as json while querying a given ite. ...
0
votes
1answer
141 views

Binary tree “get open list” SQL query

I have a node table that looks like this, plus it has an inserted_at column: id | parent_id ----------+----------- 1 | nil 2 | 1 3 | 1 4 | 2 5 | 2 ...
0
votes
1answer
99 views

DB schema for storing the process tree of linux

I search a db schema to store the process tree of linux every half hour. Platform: PostgreSQL on Linux. I am unsure where to split all the data into columns or to use a json column. Which one is ...
4
votes
1answer
978 views

For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE

I have a typical tree structure stored as an adjacency list in PostgreSQL 9.4: gear_category ( id INTEGER PRIMARY KEY, name TEXT, parent_id INTEGER ); As well as a list of items attached ...
2
votes
1answer
457 views

“No Loop” Constraint for hierarchical data in postgresql

I've got table like this in postgres: id primary key parent_id foreign key on id some data columns I need no a constrain which prevents cycles on the parent relations. There might be more tree in ...
2
votes
1answer
170 views

How to select nodes where all children is satisfied?

I have a tree structure of light bulbs. I want to turn on all the light bulbs starting from the leafs of the tree. A light bulb cannot be turned on unless all its immediate children are turned on. ...
1
vote
2answers
1k views

Performing SELECT on EACH ROW in CTE or Nested QUERY?

This is a problem in PostgreSQL I have a table which stores the tree of users; +------+---------+ | id | parent | |------+---------| | 1 | 0 | |------|---------| ...
6
votes
1answer
5k views

PostgreSQL tree structure and recursive CTE optimization

I'm trying to represent a tree structure in PostgreSQL (8.4) to be able to query the path from the root to a given node or to find all the nodes within a sub-branch. Here is a test table: CREATE ...