All Questions
Tagged with recursive postgresql
102 questions
0
votes
1
answer
140
views
How to have CTE query recurse parent query result one by one?
I have this query used on Postgres to return place administrative levels.
WITH RECURSIVE hierarchy(name, pinyin, level) AS (
SELECT p.name, p.pinyin, p.level, p.upper_place_object_id_fk
from ...
- 101
5
votes
1
answer
685
views
Grouping by overlapping arrays, transitively, without duplicates
I've found:
Group by array overlapping
https://stackoverflow.com/a/23650080/5419599
However, I'm having trouble putting it to use in my case.
I have a table like so (the real myid values are hashes,...
- 587
0
votes
1
answer
638
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 ...
- 111
0
votes
0
answers
76
views
Cleaner approach for Recursive CTE query
I have a table called entry_changes defined as follows:
Table "public.entry_changes"
Column | Type | ...
- 436
2
votes
0
answers
765
views
Is there a way to make postgres count planned number of rows when using recurcive CTE?
Postgres has no ideas about expected rows when using recursive CTE. Here is an expamle:
create table test_recursive (
id serial primary key,
parent_id int null references test_recursive,
name text ...
- 46
0
votes
1
answer
279
views
Removing duplicate-ish paths generated from recursive CTE
I have some multi-parented data, that I run a recursive CTE against to find common ancestors:
item_id |item_name |path_name |id_path_array |
--------|----------|-------------|--------------|
1 ...
- 105
3
votes
2
answers
1k
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 ...
- 131
9
votes
2
answers
7k
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 ...
- 123
2
votes
0
answers
71
views
Unrolling an entity-matching table
I have a table with the following structure:
create table pairs (
block text,
left_entity int,
right_entity int
);
That table was the result of a record linkage process. One of the ...
- 123
2
votes
1
answer
2k
views
WITH RECURSIVE with union among the recursive term
Is it possible to use WITH RECURSIVE queries in PostgreSQL where the recursive term is a union of different tables? My usecase is to extract some tuples from a table and some logical rules:
And I ...
- 123
1
vote
0
answers
260
views
Postgres recursive query not using trivial index on massive table
I've got a simple recursive query to find a tree of all posts:
with recursive all_mentions(post_id, author_id, mentioned_id) as (
select post_id, author_id, mention_id
from mentions
where ...
- 151
3
votes
2
answers
4k
views
Parent Child Relation in same Table . Select Query to get only parent records and Records not comes under parent child relation
I have a table with Parent-Child Relation in the same Table. Some Sample Data
id │ name │ parent │ is_active
════╪══════════════════════╪════════╪═══════════
1 │ Company A ...
- 31
1
vote
1
answer
546
views
How to link rows in a many-to-many relationship where types have parents, grandparents etc
In PostgreSQL 10.4, I have a model/table category with a Foreign Key to itself. The hierarchy can extend to any number of levels: parent, grandparent etc.
I have a model/table product. Each product ...
- 141
2
votes
1
answer
1k
views
Geting rows from self-referencing single table using Postgres
I am trying to get rows from self-referencing single table in Postgres.
I got table A and I would like to get Table B. For given ProcID using Input_proc_Id I want to get another row until i get ...
- 160
2
votes
1
answer
1k
views
Recursive query to find shortest path in graph
I have an assignment to find the shortest possible path between two people in a table. The table is Company(company_name, job, name), all of them not null, but none of them unique (primary key is the ...
- 31
2
votes
1
answer
2k
views
Find X parents with recursive postgres
Have issue with building a recursive query in Postgres.
Tried with this one but it doesn't seem to work. So table user have user_id and referred_by_user_id. referred_by_user_id can be empty, if it is ...
- 133
5
votes
2
answers
8k
views
Create JSON object from recursive tree structure
Having this simple many-to-many self-referential structure.
An item owns other items through the joins table:
CREATE TABLE items (
item_id serial PRIMARY KEY
, title text
);
CREATE TABLE ...
- 179
1
vote
1
answer
3k
views
How to use WITH RECURSIVE with values in an array?
I'm sure I'm missing something simple here, but I'm trying to use PostgreSQL's WITH RECURSIVE to create a graph of children records.
The data (simplified) looks like this:
CREATE TABLE offspring (...
- 21
10
votes
2
answers
3k
views
self-joins in recursive CTE: `recursive reference must not appear more than once`?
This is tested with PostgreSQL 9.6, but it's a general SQL question.
Is it possible to use self-joins of the recursive table in a recursive CTE (rCTE)?
I tried the following rCTE containing a self-...
- 3,692
20
votes
1
answer
38k
views
CTE: Get all parents and all children in one statement
I have this working CTE example.
I can select all grand-parents and all children.
But how can I select all grand-parents and all children in one statement?
In this example I want Grandfather, Father, ...
- 1,561
0
votes
5
answers
4k
views
Finding the first parent (manager) of an employee that makes twice as much in a self-referencing table?
Example you have the following table
CREATE TABLE emp (id,name,salary,manager)
AS
VALUES
( 1 , 'james' , 10000 , null ),
( 2 , 'alex' , 5000 , 1 ),
( 3 , 'Alice' , 4500 , 1 ),
...
0
votes
1
answer
274
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
...
- 103
1
vote
1
answer
2k
views
Social network comments and posts model in PostgreSQL 9.6
I'm trying to figure out a data structure for migrating from MongoDB to PostgreSQL. Within my posts table in Postgres, I have a JSONB[] array holding comments for the post.
The thing is that each ...
Duxducis
1
vote
1
answer
3k
views
How do I ORDER BY timestamp in this recursive query?
I have a working reqursive query for my comments table.
WITH RECURSIVE comments_tree(
id,
content,
created_at,
path,
level
) AS (
SELECT
id,
content,
created_at,
ARRAY[id]...
- 113
4
votes
1
answer
4k
views
PostgresSQL recursive query running slow with low load and almost no data
I'm building a simple ACL system and the datastore is Postgres 9.6.
Permissions are assigned to groups, which can have subgroups, and people are assigned to groups.
I have a poor performing query ...
- 143
1
vote
0
answers
2k
views
Joining recursive table in PostgreSQL
Situation
I have two simple tables, one called "node_relations" and one called "node_information". The table "node_relations" represents a simple parent-child hierarchy, having only two columns "id" ...
- 119
1
vote
1
answer
2k
views
Is WITH RECURSIVE the right tool for my task?
Table setup:
create table users(id bigserial, group_id bigint);
insert into users(group_id) values (1), (1), (2), (1), (3), (3);
Query:
WITH RECURSIVE r AS (
SELECT users.id, users....
- 11
0
votes
1
answer
488
views
Using `nextval` and `currval` inside one CREATE TABLE
Let's say that we would like to model a team of people that can have subteams like so:
CREATE SEQUENCE seq_teams;
CREATE TABLE teams (
tid INTEGER PRIMARY KEY DEFAULT nextval('seq_teams'),
...
- 145
0
votes
1
answer
56
views
Doing something immediately with data in array field [closed]
I have a table that looks like this:
stuff
id
content
score
children[]
I'd like to run a query that selects all the children for a given id, and then right away gets the full records ...
3
votes
1
answer
422
views
Limited utility in RECURSIVE VIEWS?
In the docs for recursive queries with WITH, you'll find this
Recursive queries are typically used to deal with hierarchical or tree-structured data.
This seems to be true, but how does this ...
- 64.7k
7
votes
2
answers
4k
views
Looking for a simpler alternative to a recursive query
The actual query is more involved, but the problem I'm facing can be distilled to this:
A query to filter a rowset of monotonically increasing integers so that - in the final result set, row(n+1)....
- 71
4
votes
2
answers
442
views
Finding earliest connected value over two columns
I have a, perhaps slightly esoteric, use case.
Given a table like the following:
id1 | id2 | timestamp
-----+-----+---------------------
3 | 4 | 2016-03-22 09:52:15
1 | 2 | ...
3
votes
2
answers
3k
views
PostgreSQL: Calculate balance working backwards from current balance
I have a balances table that stores the current_balance for bank accounts; and a transfers table that contains a row for every transfer to- and from an account (deposits are positive numbers, ...
- 341
15
votes
2
answers
6k
views
PostgreSQL Recursive Descendant Depth
I need to calculate the depth of a descendant from it's ancestor. When a record has object_id = parent_id = ancestor_id, it is considered a root node (the ancestor). I have been trying to get a WITH ...
- 151
3
votes
1
answer
1k
views
Postgres plpgsql: Is a view better than a select query in a plpgsql function
I have a recursive query to the general effect of:
WITH RECURSIVE text1 (selfid, parentid, text) AS
(
SELECT DISTINCT
text.selfid,
text.parentid,
text.text
FROM
text
WHERE
text.selfid = ...
- 183
4
votes
1
answer
2k
views
Preserve order of array elements after join
I have a query that returns a CTE looking like
+-----------+-------------+
| node_id | ancestors |
|-----------+-------------|
| 1 | [] |
| 2 | [] |
| 3 |...
- 143
3
votes
1
answer
532
views
Need for recursive lookup, stuck in query design
In PostgreSQL 9.4, here is my table:
with r(pk, userid,partneruserid) as
(values
(1,1,2),
(2,1,3),
(3,1,6),
(4,2,5),
(5,2,6),
(6,3,1),
(7,4,1),
(8,5,3),
(9,6,2),
(10,6,3)
) select * from r;
In a ...
- 207
2
votes
1
answer
5k
views
Count children of a parent in recursive CTE
I use this query to find comments by a parent id:
WITH RECURSIVE cte (id, content, path, parent_id, depth) AS (
SELECT id,
content,
array[id] AS path,
parent_id,
...
- 167
6
votes
2
answers
4k
views
Improve performance for order by with columns from many tables
Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two ...
2
votes
2
answers
3k
views
Retrieve additional columns in recursive CTE
This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep.
I manage ...
- 167
4
votes
0
answers
984
views
Recursive BOM (Bill Of Material) type query
I have what I've been told is a Bill Of Material type query - which requires a 'recursive' query. I've never heard of such a thing - and was hoping to get some help.
My schema stores asset / asset-...
- 41
1
vote
1
answer
108
views
Finding the current prices for n Fuelstations at a certain point in time
I have a Table where price information is stored in with approx 13 million rows stored in a PostgreSQL 9.5 database.
CREATE TABLE public.de_tt_priceinfo (
id integer NOT NULL DEFAULT nextval('...
- 298
1
vote
1
answer
1k
views
Determine existence in recursive working table
I have a query that needs to check for existence of each input row type in another table, but it is unnecessary and inefficient to check for existence of subsequent rows of the same row type if one is ...
- 555
7
votes
1
answer
2k
views
Recursive CTE to find unique slug
I have an article table where I want the slug to be unique.
CREATE TABLE article (
title char(50) NOT NULL,
slug char(50) NOT NULL
);
When the user enters a title e.g. News on Apple, I want ...
- 1,119
1
vote
1
answer
773
views
hierarchical retreival of records in postgresql
I am using postgresql-9.2 . I have a table of products which stores the information about the products and its child table is content table , which contains the products linked to it.
PRODUCT MASTER ...
- 113
3
votes
1
answer
389
views
PL/pgSQL function or rCTE to detect depth of relations between two tables
I need to perform a PostgreSQL Function for checking the depth of relations. There is a circular binding from table a to b, back to another element in a and, in some cases back again to b. These ...
- 31
2
votes
1
answer
4k
views
Recursive calls in stored procedure and temporary tables
I am trying to create a stored procedure in PostgreSQL which has recursive calls. The procedure creates a temporary table to store results. The temporary table isn't dropped after first call due to ...
- 33
7
votes
2
answers
6k
views
Query to find circular references
I have two tables
ID Task
1 1
2 2
3 3
4 4
Col1 depend
2 3
2 4
3 1
4 2
ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID ...
- 111
19
votes
1
answer
14k
views
How do I sort the results of a recursive query in an expanded tree-like fashion?
Let's assume you have a nodes tables like this:
CREATE TABLE nodes (
node serial PRIMARY KEY,
parent integer NULL REFERENCES nodes(node),
ts timestamp NOT NULL DEFAULT now()
);
It ...
- 525
1
vote
1
answer
2k
views
Self-join unique pairs
I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is ...
- 588