All Questions
Tagged with recursive postgresql
102 questions
1
vote
2
answers
46
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
16
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
1
answer
62
views
PostgreSQL CTE Recursion - How to Improve Performance for Parent->Child Tree (8 levels deep, 890 roots)
I am working on a query that performs PostgreSQL CTE Recursion in PostgreSQL 13. This query is intended to recurse from the root nodes to the deepest leaf node with certain properties (labeled 'begat'...
- 50
0
votes
1
answer
183
views
What is the best way in Postgres to inherit values from parent row recursively if not provided?
Is there a pattern in SQL where a child row inherits empty (null?) values from a parent?
E.g., given the following 'chickens' table (sorry):
id
parent_id
name
flys
noise
weight
egg_color
...
- 101
0
votes
0
answers
35
views
How to match rows from relationship via a recursive CTE
Apologies for the terrible question title, I couldn't think how to describe this question in a single line.
I have a schema that looks roughly like this: https://dbdiagram.io/d/...
- 163
1
vote
1
answer
114
views
PostgreSQL recursive query performance improvement
I'm building a database which is used to derive hierarchical relations for which I chose a PostgreSQL recursive CTE .
my table structure is
table_name |column_name |data_type ...
- 13
0
votes
1
answer
533
views
How can I recursively query a table containing LTREE to get the tree like structure when some of the parent paths are missing?
Lets say I have this table:
CREATE TABLE nodes (node_path ltree);
INSERT INTO nodes VALUES ('Top.Science');
INSERT INTO nodes VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO nodes VALUES ('...
0
votes
1
answer
241
views
"ON UPDATE" in self referencing table
I have a table Category with a parent_id column referencing the self id column, for this, I used "ON DELETE CASCADE" meaning if the parent is deleted, other children would be deleted too.
At ...
- 23
3
votes
1
answer
356
views
Return recursive self joins in PostgreSQL with aggregated data
I have this simple table schema:
SQL Fiddle
PostgreSQL 9.6 Schema Setup:
CREATE TABLE IF NOT EXISTS users(
id INT NOT NULL PRIMARY KEY, email TEXT NOT NULL UNIQUE,
password TEXT NOT NULL, ...
- 165
0
votes
1
answer
215
views
Why PostgreSQL RECURSIVE query iteration does not return the same result as manually created one?
I have the following test table:
SELECT * FROM attempts order by id;
id | user_id | name
----+---------+------
1 | 1 | a
2 | 2 | b
3 | 1 | b
4 | 1 | c
5 | 2 | ...
- 121
1
vote
2
answers
556
views
How does PostgreSQL recursive query treat SELECT MIN(ID) FROM T on each iteration?
I have the following query that I do not understand how it works, especially the subquery that returns the minimum ID without grouping.
WITH RECURSIVE t(id, user_id, name) AS (
select min(id) - 1, ...
- 121
1
vote
0
answers
50
views
Recursive CTE get
I have the following tables:
Table1
id
entity_id
value
Table2 - Same structure as Table1
id
entity_id
value
Table3
source_id
destination_id
entity_id
Table1 and Table2 are both partitions from ...
- 11
0
votes
0
answers
173
views
How to use variable in the select query PostgreSQL
I'm looking for a way to write a recursive query, but I'm getting an error.
"SQL Error [42601]: ERROR: syntax error at or near "with recursive"
Position: 287"
query result "...
- 1
1
vote
1
answer
1k
views
Why does a Postgres recursive CTE need the list of columns as argument?
This is kind of a subjective question.
If I have a recursive CTE something like:
WITH RECURSIVE r(x, y) AS ( ...
SELECT x, y -- select1
FROM t WHERE ..
UNION
SELECT t.x, t.y -- ...
- 823
3
votes
1
answer
2k
views
Concatenate all previous row values from a TEXT column in reverse order
My Postgres version is 11.15. I have a table like:
id
employee
1
Anna
2
Bill
3
Carol
4
Dave
5
Tom
The output I need is
id
cumulative_employees
1
Anna
2
Bill, Anna
3
Carol, Bill, Anna
4
...
0
votes
0
answers
385
views
How to optimize (or get an alternative faster solution to) this recursive query that groups using recursive CTEs in Postgres?
I am on the quest to implement an NMS filter for labels in a Database.
For context, here's a link to my first question, where I explain what NMS is and what I'm trying to achieve (I will edit that ...
- 51
0
votes
1
answer
116
views
Optimizing query that finds closest unique records
I've been trying out different queries to get the best performance when querying hierarchical data on Postgres 13.6.
Fiddle: https://dbfiddle.uk/060f_h96
Explain: https://explain.dalibo.com/plan/...
1
vote
0
answers
121
views
How to keep up-to-date sums in the tree?
I have a tree table. I need to ensure that when inserting or updating a record, the sumvalue field of all parents is updated - this is the sum of all child elements. I wrote a trigger (even a query is ...
- 119
4
votes
1
answer
2k
views
How to optimize a recursive query in Postgresql?
I have a recursive query that takes too long - 30+ ms where doing the individual queries to extract the same data manually takes < 0.12 ms. So we're talking 250x as long.
I have the following ...
- 187
2
votes
1
answer
1k
views
Stopping recursive query on first match in Postgres 11
Im struggling a bit with recursive queries in Postgres.
Given these tables:
create table resource_v3
(
id uuid not null primary key,
name varchar(255),
resource_id ...
- 127
0
votes
1
answer
177
views
Calculate the number of days that the field value has not changed
Please consider the following table:
User
Date
amount
John
1/1/2022
40
John
1/2/2022
-30
John
1/6/2022
50
...
...
...
Jack
4/1/2022
80
Jack
4/1/2022
-60
We have three records for John and two ...
- 115
1
vote
0
answers
76
views
Splitting time period into multiple with irregular length, grouped by name
We have a set of data containing event dates, other event data and the start and end date that of the 'event season' that the event took place in.
The issue is as follows: In postgresql, I wish to ...
- 123
2
votes
1
answer
963
views
postgresql Recursive Update resulting in Sequential Scan
I have a table with a self-referential foreign key, and I would like to update a given parent, and all of the descendants of the parent, with the same recursive update.
When using an UPDATE with a ...
- 207
0
votes
0
answers
1k
views
SQL (postgres) - Recursive view for all parents of a child
Solved: Solution from crossposting: https://stackoverflow.com/a/71149082/8082262
I have two tables: community2collection(collection_id,community_id) and community2community(child_comm_id,...
2
votes
1
answer
766
views
Recursive CTE in PostgreSQL to generate data with different frequencies
I am trying to write a data generation query which uses recursive CTE in Postgresql 14.
Consider schema "sc" which includes a function getfreq. getfreq takes an int as param (which ...
- 187
6
votes
3
answers
1k
views
How can I calculate all grouping permutations of an input string in SQL?
Given an input like "ABC" generate a query that calculates all potential splits of 0 or more of the given string,
Desired output,
A B C
A BC
AB C
ABC
Given an input like "ABCD&...
- 64.7k
4
votes
2
answers
2k
views
Generate all combinations of a list of strings
I'm trying to generate all combinations of a list of Strings
list = ['A', 'B', 'C', 'D'].
I want to generate all possibilities and then search in the DB
ABCD, ABC, ABD, ACD, BCD, AB, AC, AD, BC, BD, ...
- 43
0
votes
1
answer
79
views
How can I include an entry for an intermediate row In a recursive query summing up children
I have a schema with activity(activity_id, parent_activity_id, name) and period(activity_id, duration, etc).
An activity can have ZERO to many associated periods. Further, activities have a reference ...
- 121
0
votes
1
answer
2k
views
How to eliminate duplicates made by recursive CTE [closed]
I have following schema in PostgreSQL 12 (objects and their relations are versioned by date ranges):
CREATE TABLE tmp_deps (
id bigint,
code text,
name text,
start_date date,
...
- 143
3
votes
2
answers
560
views
Optimization of recursive view
I have an optimization problem when using a recursive view on PostgreSQL. When I perform a trivial query using this view, the execution time is abnormally long.
To expose my problem, here is a ...
0
votes
2
answers
4k
views
Postgresql split single row into multiple rows based on date [duplicate]
In my case, we have a table ABC with the supposed 1 row.
id
name
start_date
end_date
1
Rahul
01/01/2021
06/01/2021
but while retrieving I would like to get multiple rows where the start date is ...
- 115
5
votes
1
answer
2k
views
How to turn a set of flat trees into a single tree with multiple leaves?
We have this beautiful Postgres tree generator. Yet it kind of produces cuts of a tree not a whole tree all at once:
item_id jsonb_pretty
1 {
"title": "PARENT",
"...
- 75
0
votes
2
answers
632
views
How to limit tree traversal in case of loops?
So I want to limit tree depth traversal (as the simplest recursion killer tool). Example code that turns normalized records into tree:
CREATE TABLE items (
item_id serial PRIMARY KEY,
title ...
- 75
2
votes
1
answer
440
views
How can I change string output in a hierarchy tree with PostgreSQL?
I have a table like this:
-------------------------------------------------
| id | description | parent_id | cost
--------------------------------------------------
| 1 | Radiology ...
- 37
9
votes
2
answers
1k
views
How can I find the shortest path between two currencies?
I have table currency_pair(c1, c2); values are (usd,bnb), (cake,bnb), (cake,eth).
I need to find the shortest path that allows me to compare usd to eth.
The result here would be those same values. I ...
- 1,274
1
vote
1
answer
389
views
Define recursive composite types
Is it possible to define recursive composite types in Postgres 13?
create type "t" as (
"a" int,
"b" t
);
I am aware that I can use a table with a parent-child ...
- 31
3
votes
1
answer
5k
views
Recursively find the path to all leaves descending from a given parent in a tree
I'm trying to write a query to identify the path to each furthest descendant of a particular parent node in a table of tree structures like:
0 1
| |
2 3
| |
4 5
...
- 155
2
votes
0
answers
485
views
Drop intermediate results of recursive query in Postgres
I'm trying to aggregate the contents of a column from a directed acyclic graph (DAG) stored in a Postgres table.
Each dag row has an id, bytes, and may also have a parent referencing another row. I'm ...
- 155
-1
votes
3
answers
3k
views
Is there really no SIMPLE way to achieve "recursive lookup" in PostgreSQL?
I have a table of categories:
id,label,parent_id
1,Animals,null
2,Dogs,1
3,Cats,1
4,Black,3
This means that "Animals" is a top-level category, and "Dogs" and "Cats" are ...
- 161
5
votes
1
answer
300
views
Determine Nodes in Network with PostgreSQL
I have a table where every entry is a node and the table contains the direct connections of each node to other nodes. I am looking to create a view with a column for each node containing all the nodes ...
- 53
0
votes
1
answer
146
views
calculate or get price of parent items on bill of materials
i want to calculate or get the price of parent items on bill of materials query
here is the fiddle-> https://www.db-fiddle.com/f/o3jLgZxKNLG14mna8QGdVN/6
please note in the second row, betax has ...
- 9
0
votes
1
answer
3k
views
How to aggregate over a "multi source" with recursive, in postgres?
Imagine I have a table "departments" like this:
name TEXT
parent_department TEXT (nullable)
I also have a "budgets" table like this:
department_name TEXT
budget INTEGER
I need to "traverse" this ...
- 330
7
votes
1
answer
1k
views
Recursivly get a Tree Via self joined table
Using other questions here and Postgresql documentation I've managed to build a many-to-many self joined table.
However adding a WHERE clause is giving me trouble.
Problem:
A Category can have many ...
- 171
0
votes
1
answer
2k
views
Calculate products count based on category tree table
It is necessary to calculate how many products were created in each category, of course, that the products created in a child must be considered for the parent.
How can i do that SQL/Postgresql?
...
- 3
1
vote
1
answer
2k
views
54001 stack depth limit exceeded in recursive stable function [closed]
I have a function which calls itself to resolve a hierarchy. Without any volatility declaration, it gives me the desired result (that means the recursion ends). As a performance optimization, I ...
- 317
7
votes
1
answer
4k
views
Recursive CTE based on function values significantly slower on Postgres 12 than on 11
Following up on my question about some queries in Postgres 12 being slower than in 11 I think, I was able to narrow down the problem. It seems like one recursive CTE based on function values is the ...
- 479
1
vote
1
answer
84
views
How can I create a recursive query over a column that has gaps?
I've adapted the recursive query from here in order to calculate ELO ratings (like in chess) from a list of game outcomes on-the-fly.
Given a table of games like:
id | home_player | away_player | ...
- 113
1
vote
1
answer
454
views
How to use a recursive CTE to get ancestors in a hierarchy
create table division (
id serial primary key,
name varchar not null
);
-- nested sets table
create table location (
id serial primary key,
name varchar not null,
division_id integer not ...
- 111
0
votes
2
answers
2k
views
How to recursively update a same string?
I have a table with a column containing names like this:
id, employee
1, Mr. John Cole Thornton
2, Mr. Paul George Mckenzie
3, Mr. George Mick McDoughal
4, Ms. Emily Suzan Flemming
5, Mr. Alan ...
- 191
3
votes
2
answers
657
views
Rolling sum aggregates with self-referencing conditions
I have what I believe is a use-case for a recursive CTE, but I've yet to figure out how to structure it. The requirement is to create a rolling sum total over an amortization schedule, but payments ...
- 116