Postgres Retrieve all id in a tree for given subnode
up vote
5
down vote
favorite
I have a non-binary tree of customer, and I need to obtain all the id in a tree for the given node.
The table is very simple just an join table with parent id and child id
This is a representation of the tree I stored in my db.
In this example if I search for node 17 I need in return 14-17
if I search for 11 I need in return 1-6-5-4-8-11-12-7-2-10-3
The order is not important I only need the id to avoid circularity when add children to a node.
I created this query.
The ancestor part works fine, I retrieve all parent nodes,
but for the descendants I have some trouble.
I'm only able to retrieve some part of the tree.
For example, with node 11 I retrieve 4-10-6-11-7-8, so all right part of the tree is missing
WITH RECURSIVE
-- starting node(s)
starting (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.child = :node or t.parent = :node
)
,
ancestors (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.parent IN (SELECT parent FROM starting)
UNION ALL
SELECT t.parent, t.child
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.parent IN (SELECT parent FROM starting) or t.child in (select child from starting)
UNION ALL
SELECT t.parent, t.child
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.parent = a.child
)
table ancestors
union all
table descendants
UPDATE
I see that many example include in the tree table also the root in form (root_id, null)
in my case i don't have this record.
For example taking the smallest tree 14->17, in my table i have only one record
parent, child
14 17
postgresql recursive tree
New contributor
add a comment |
up vote
5
down vote
favorite
I have a non-binary tree of customer, and I need to obtain all the id in a tree for the given node.
The table is very simple just an join table with parent id and child id
This is a representation of the tree I stored in my db.
In this example if I search for node 17 I need in return 14-17
if I search for 11 I need in return 1-6-5-4-8-11-12-7-2-10-3
The order is not important I only need the id to avoid circularity when add children to a node.
I created this query.
The ancestor part works fine, I retrieve all parent nodes,
but for the descendants I have some trouble.
I'm only able to retrieve some part of the tree.
For example, with node 11 I retrieve 4-10-6-11-7-8, so all right part of the tree is missing
WITH RECURSIVE
-- starting node(s)
starting (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.child = :node or t.parent = :node
)
,
ancestors (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.parent IN (SELECT parent FROM starting)
UNION ALL
SELECT t.parent, t.child
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.parent IN (SELECT parent FROM starting) or t.child in (select child from starting)
UNION ALL
SELECT t.parent, t.child
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.parent = a.child
)
table ancestors
union all
table descendants
UPDATE
I see that many example include in the tree table also the root in form (root_id, null)
in my case i don't have this record.
For example taking the smallest tree 14->17, in my table i have only one record
parent, child
14 17
postgresql recursive tree
New contributor
Depending on your workload and use case you may also want to considerltree
– Evan Carroll
13 hours ago
add a comment |
up vote
5
down vote
favorite
up vote
5
down vote
favorite
I have a non-binary tree of customer, and I need to obtain all the id in a tree for the given node.
The table is very simple just an join table with parent id and child id
This is a representation of the tree I stored in my db.
In this example if I search for node 17 I need in return 14-17
if I search for 11 I need in return 1-6-5-4-8-11-12-7-2-10-3
The order is not important I only need the id to avoid circularity when add children to a node.
I created this query.
The ancestor part works fine, I retrieve all parent nodes,
but for the descendants I have some trouble.
I'm only able to retrieve some part of the tree.
For example, with node 11 I retrieve 4-10-6-11-7-8, so all right part of the tree is missing
WITH RECURSIVE
-- starting node(s)
starting (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.child = :node or t.parent = :node
)
,
ancestors (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.parent IN (SELECT parent FROM starting)
UNION ALL
SELECT t.parent, t.child
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.parent IN (SELECT parent FROM starting) or t.child in (select child from starting)
UNION ALL
SELECT t.parent, t.child
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.parent = a.child
)
table ancestors
union all
table descendants
UPDATE
I see that many example include in the tree table also the root in form (root_id, null)
in my case i don't have this record.
For example taking the smallest tree 14->17, in my table i have only one record
parent, child
14 17
postgresql recursive tree
New contributor
I have a non-binary tree of customer, and I need to obtain all the id in a tree for the given node.
The table is very simple just an join table with parent id and child id
This is a representation of the tree I stored in my db.
In this example if I search for node 17 I need in return 14-17
if I search for 11 I need in return 1-6-5-4-8-11-12-7-2-10-3
The order is not important I only need the id to avoid circularity when add children to a node.
I created this query.
The ancestor part works fine, I retrieve all parent nodes,
but for the descendants I have some trouble.
I'm only able to retrieve some part of the tree.
For example, with node 11 I retrieve 4-10-6-11-7-8, so all right part of the tree is missing
WITH RECURSIVE
-- starting node(s)
starting (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.child = :node or t.parent = :node
)
,
ancestors (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.parent IN (SELECT parent FROM starting)
UNION ALL
SELECT t.parent, t.child
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.parent IN (SELECT parent FROM starting) or t.child in (select child from starting)
UNION ALL
SELECT t.parent, t.child
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.parent = a.child
)
table ancestors
union all
table descendants
UPDATE
I see that many example include in the tree table also the root in form (root_id, null)
in my case i don't have this record.
For example taking the smallest tree 14->17, in my table i have only one record
parent, child
14 17
postgresql recursive tree
postgresql recursive tree
New contributor
New contributor
edited 14 hours ago
New contributor
asked 16 hours ago
Luca Nitti
283
283
New contributor
New contributor
Depending on your workload and use case you may also want to considerltree
– Evan Carroll
13 hours ago
add a comment |
Depending on your workload and use case you may also want to considerltree
– Evan Carroll
13 hours ago
Depending on your workload and use case you may also want to consider
ltree
– Evan Carroll
13 hours ago
Depending on your workload and use case you may also want to consider
ltree
– Evan Carroll
13 hours ago
add a comment |
2 Answers
2
active
oldest
votes
up vote
4
down vote
accepted
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, then this would be just itself.
- Then find the descendants of all those ancestors (including themselves). We may have several nodes in the
ancestors
result set, we may get duplicates here, so we useUNION
(and notUNION ALL
) to remove them. - Note that the query will work even if the input node is a root with has no children.
- It will also work if the data set is not a forest of trees but an arbitrary directed graph (where nodes can have more than one parent).
The query:
WITH RECURSIVE
ancestors (parent) AS
(
SELECT :node -- start with the given node
UNION ALL
SELECT t.parent -- and find all its ancestors
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (customer) AS
(
SELECT parent AS customer -- now start with all the ancestors
FROM ancestors
UNION
SELECT t.child -- and find all their descendants
FROM public.customerincustomer AS t JOIN descendants AS d ON t.parent = d.customer
)
SELECT customer
FROM descendants ;
Thanks! this is exactly what i need
– Luca Nitti
13 hours ago
add a comment |
up vote
3
down vote
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 RECURSIVE get_parent AS
(
SELECT
t1.id,
t1.parent_id,
t1.name,
0 AS level
FROM
tree t1
WHERE
t1.id = node_id
UNION ALL
SELECT
t2.id,
t2.parent_id,
t2.name,
level+1
FROM
tree t2
INNER JOIN
get_parent ON get_parent.parent_id = t2.id
)
SELECT
id
FROM
get_parent
ORDER BY
level DESC
LIMIT 1 ;
$$
LANGUAGE SQL;
select get_parent(7);
| get_parent |
| ---------: |
| 6 |
Now, next query returns the whole tree structure based on a parent node.
WITH RECURSIVE childs AS
(
SELECT
t1.id,
t1.parent_id,
t1.name
FROM
tree t1
WHERE
t1.id = get_parent(7)
UNION ALL
SELECT
t2.id,
t2.parent_id,
t2.name
FROM
tree t2
INNER JOIN
childs ON childs.id = t2.parent_id
)
SELECT
id,
parent_id,
name
FROM
childs;
id | parent_id | name
-: | --------: | :------
6 | 1 | Node 6
4 | 6 | Node 4
8 | 6 | Node 8
11 | 6 | Node 11
7 | 11 | Node 7
10 | 7 | Node 10
db<>fiddle here
as i said, i don't have problem with ancestors. I need the whole tree.
– Luca Nitti
16 hours ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Luca Nitti is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224871%2fpostgres-retrieve-all-id-in-a-tree-for-given-subnode%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
4
down vote
accepted
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, then this would be just itself.
- Then find the descendants of all those ancestors (including themselves). We may have several nodes in the
ancestors
result set, we may get duplicates here, so we useUNION
(and notUNION ALL
) to remove them. - Note that the query will work even if the input node is a root with has no children.
- It will also work if the data set is not a forest of trees but an arbitrary directed graph (where nodes can have more than one parent).
The query:
WITH RECURSIVE
ancestors (parent) AS
(
SELECT :node -- start with the given node
UNION ALL
SELECT t.parent -- and find all its ancestors
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (customer) AS
(
SELECT parent AS customer -- now start with all the ancestors
FROM ancestors
UNION
SELECT t.child -- and find all their descendants
FROM public.customerincustomer AS t JOIN descendants AS d ON t.parent = d.customer
)
SELECT customer
FROM descendants ;
Thanks! this is exactly what i need
– Luca Nitti
13 hours ago
add a comment |
up vote
4
down vote
accepted
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, then this would be just itself.
- Then find the descendants of all those ancestors (including themselves). We may have several nodes in the
ancestors
result set, we may get duplicates here, so we useUNION
(and notUNION ALL
) to remove them. - Note that the query will work even if the input node is a root with has no children.
- It will also work if the data set is not a forest of trees but an arbitrary directed graph (where nodes can have more than one parent).
The query:
WITH RECURSIVE
ancestors (parent) AS
(
SELECT :node -- start with the given node
UNION ALL
SELECT t.parent -- and find all its ancestors
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (customer) AS
(
SELECT parent AS customer -- now start with all the ancestors
FROM ancestors
UNION
SELECT t.child -- and find all their descendants
FROM public.customerincustomer AS t JOIN descendants AS d ON t.parent = d.customer
)
SELECT customer
FROM descendants ;
Thanks! this is exactly what i need
– Luca Nitti
13 hours ago
add a comment |
up vote
4
down vote
accepted
up vote
4
down vote
accepted
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, then this would be just itself.
- Then find the descendants of all those ancestors (including themselves). We may have several nodes in the
ancestors
result set, we may get duplicates here, so we useUNION
(and notUNION ALL
) to remove them. - Note that the query will work even if the input node is a root with has no children.
- It will also work if the data set is not a forest of trees but an arbitrary directed graph (where nodes can have more than one parent).
The query:
WITH RECURSIVE
ancestors (parent) AS
(
SELECT :node -- start with the given node
UNION ALL
SELECT t.parent -- and find all its ancestors
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (customer) AS
(
SELECT parent AS customer -- now start with all the ancestors
FROM ancestors
UNION
SELECT t.child -- and find all their descendants
FROM public.customerincustomer AS t JOIN descendants AS d ON t.parent = d.customer
)
SELECT customer
FROM descendants ;
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, then this would be just itself.
- Then find the descendants of all those ancestors (including themselves). We may have several nodes in the
ancestors
result set, we may get duplicates here, so we useUNION
(and notUNION ALL
) to remove them. - Note that the query will work even if the input node is a root with has no children.
- It will also work if the data set is not a forest of trees but an arbitrary directed graph (where nodes can have more than one parent).
The query:
WITH RECURSIVE
ancestors (parent) AS
(
SELECT :node -- start with the given node
UNION ALL
SELECT t.parent -- and find all its ancestors
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (customer) AS
(
SELECT parent AS customer -- now start with all the ancestors
FROM ancestors
UNION
SELECT t.child -- and find all their descendants
FROM public.customerincustomer AS t JOIN descendants AS d ON t.parent = d.customer
)
SELECT customer
FROM descendants ;
edited 13 hours ago
answered 13 hours ago
ypercubeᵀᴹ
73.7k11124203
73.7k11124203
Thanks! this is exactly what i need
– Luca Nitti
13 hours ago
add a comment |
Thanks! this is exactly what i need
– Luca Nitti
13 hours ago
Thanks! this is exactly what i need
– Luca Nitti
13 hours ago
Thanks! this is exactly what i need
– Luca Nitti
13 hours ago
add a comment |
up vote
3
down vote
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 RECURSIVE get_parent AS
(
SELECT
t1.id,
t1.parent_id,
t1.name,
0 AS level
FROM
tree t1
WHERE
t1.id = node_id
UNION ALL
SELECT
t2.id,
t2.parent_id,
t2.name,
level+1
FROM
tree t2
INNER JOIN
get_parent ON get_parent.parent_id = t2.id
)
SELECT
id
FROM
get_parent
ORDER BY
level DESC
LIMIT 1 ;
$$
LANGUAGE SQL;
select get_parent(7);
| get_parent |
| ---------: |
| 6 |
Now, next query returns the whole tree structure based on a parent node.
WITH RECURSIVE childs AS
(
SELECT
t1.id,
t1.parent_id,
t1.name
FROM
tree t1
WHERE
t1.id = get_parent(7)
UNION ALL
SELECT
t2.id,
t2.parent_id,
t2.name
FROM
tree t2
INNER JOIN
childs ON childs.id = t2.parent_id
)
SELECT
id,
parent_id,
name
FROM
childs;
id | parent_id | name
-: | --------: | :------
6 | 1 | Node 6
4 | 6 | Node 4
8 | 6 | Node 8
11 | 6 | Node 11
7 | 11 | Node 7
10 | 7 | Node 10
db<>fiddle here
as i said, i don't have problem with ancestors. I need the whole tree.
– Luca Nitti
16 hours ago
add a comment |
up vote
3
down vote
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 RECURSIVE get_parent AS
(
SELECT
t1.id,
t1.parent_id,
t1.name,
0 AS level
FROM
tree t1
WHERE
t1.id = node_id
UNION ALL
SELECT
t2.id,
t2.parent_id,
t2.name,
level+1
FROM
tree t2
INNER JOIN
get_parent ON get_parent.parent_id = t2.id
)
SELECT
id
FROM
get_parent
ORDER BY
level DESC
LIMIT 1 ;
$$
LANGUAGE SQL;
select get_parent(7);
| get_parent |
| ---------: |
| 6 |
Now, next query returns the whole tree structure based on a parent node.
WITH RECURSIVE childs AS
(
SELECT
t1.id,
t1.parent_id,
t1.name
FROM
tree t1
WHERE
t1.id = get_parent(7)
UNION ALL
SELECT
t2.id,
t2.parent_id,
t2.name
FROM
tree t2
INNER JOIN
childs ON childs.id = t2.parent_id
)
SELECT
id,
parent_id,
name
FROM
childs;
id | parent_id | name
-: | --------: | :------
6 | 1 | Node 6
4 | 6 | Node 4
8 | 6 | Node 8
11 | 6 | Node 11
7 | 11 | Node 7
10 | 7 | Node 10
db<>fiddle here
as i said, i don't have problem with ancestors. I need the whole tree.
– Luca Nitti
16 hours ago
add a comment |
up vote
3
down vote
up vote
3
down vote
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 RECURSIVE get_parent AS
(
SELECT
t1.id,
t1.parent_id,
t1.name,
0 AS level
FROM
tree t1
WHERE
t1.id = node_id
UNION ALL
SELECT
t2.id,
t2.parent_id,
t2.name,
level+1
FROM
tree t2
INNER JOIN
get_parent ON get_parent.parent_id = t2.id
)
SELECT
id
FROM
get_parent
ORDER BY
level DESC
LIMIT 1 ;
$$
LANGUAGE SQL;
select get_parent(7);
| get_parent |
| ---------: |
| 6 |
Now, next query returns the whole tree structure based on a parent node.
WITH RECURSIVE childs AS
(
SELECT
t1.id,
t1.parent_id,
t1.name
FROM
tree t1
WHERE
t1.id = get_parent(7)
UNION ALL
SELECT
t2.id,
t2.parent_id,
t2.name
FROM
tree t2
INNER JOIN
childs ON childs.id = t2.parent_id
)
SELECT
id,
parent_id,
name
FROM
childs;
id | parent_id | name
-: | --------: | :------
6 | 1 | Node 6
4 | 6 | Node 4
8 | 6 | Node 8
11 | 6 | Node 11
7 | 11 | Node 7
10 | 7 | Node 10
db<>fiddle here
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 RECURSIVE get_parent AS
(
SELECT
t1.id,
t1.parent_id,
t1.name,
0 AS level
FROM
tree t1
WHERE
t1.id = node_id
UNION ALL
SELECT
t2.id,
t2.parent_id,
t2.name,
level+1
FROM
tree t2
INNER JOIN
get_parent ON get_parent.parent_id = t2.id
)
SELECT
id
FROM
get_parent
ORDER BY
level DESC
LIMIT 1 ;
$$
LANGUAGE SQL;
select get_parent(7);
| get_parent |
| ---------: |
| 6 |
Now, next query returns the whole tree structure based on a parent node.
WITH RECURSIVE childs AS
(
SELECT
t1.id,
t1.parent_id,
t1.name
FROM
tree t1
WHERE
t1.id = get_parent(7)
UNION ALL
SELECT
t2.id,
t2.parent_id,
t2.name
FROM
tree t2
INNER JOIN
childs ON childs.id = t2.parent_id
)
SELECT
id,
parent_id,
name
FROM
childs;
id | parent_id | name
-: | --------: | :------
6 | 1 | Node 6
4 | 6 | Node 4
8 | 6 | Node 8
11 | 6 | Node 11
7 | 11 | Node 7
10 | 7 | Node 10
db<>fiddle here
edited 11 hours ago
answered 16 hours ago
McNets
14.2k41754
14.2k41754
as i said, i don't have problem with ancestors. I need the whole tree.
– Luca Nitti
16 hours ago
add a comment |
as i said, i don't have problem with ancestors. I need the whole tree.
– Luca Nitti
16 hours ago
as i said, i don't have problem with ancestors. I need the whole tree.
– Luca Nitti
16 hours ago
as i said, i don't have problem with ancestors. I need the whole tree.
– Luca Nitti
16 hours ago
add a comment |
Luca Nitti is a new contributor. Be nice, and check out our Code of Conduct.
Luca Nitti is a new contributor. Be nice, and check out our Code of Conduct.
Luca Nitti is a new contributor. Be nice, and check out our Code of Conduct.
Luca Nitti is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224871%2fpostgres-retrieve-all-id-in-a-tree-for-given-subnode%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Depending on your workload and use case you may also want to consider
ltree
– Evan Carroll
13 hours ago