Postgres get all possible origin/destination node sequence
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I have this Postgres table with node of a directed graph:
node_id | node_sequence
-----------------------
1 1
2 2
3 3
I'd return a table with all the possible origin destination sequence (only in one direction) between the node :
(1,2);(1,2,3);(2,3) . So the output table should be:
node_id
----
1
2
1
2
3
2
3
maybe WITH RECURSIVE is the right thing to do but I cannot understand how.
thanks a lot
postgresql window-functions with-statement
asked Nov 16 '18 at 14:00
franco_bfranco_b
1861418
add a comment |
0
I have this Postgres table with node of a directed graph:
node_id | node_sequence
-----------------------
1 1
2 2
3 3
I'd return a table with all the possible origin destination sequence (only in one direction) between the node :
(1,2);(1,2,3);(2,3) . So the output table should be:
node_id
----
1
2
1
2
3
2
3
maybe WITH RECURSIVE is the right thing to do but I cannot understand how.
thanks a lot
postgresql window-functions with-statement
asked Nov 16 '18 at 14:00
franco_bfranco_b
1861418
1
What are you defining as "combinations" ofa?
– Tim Biegeleisen
Nov 16 '18 at 14:06
I edit the question. I'd get all possible value combinations
– franco_b
Nov 16 '18 at 14:10
Why not {1,3} ? why not the sigletons {1}, {2}, {3} ?
– joop
Nov 16 '18 at 14:31
It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer was not clear. I just edit it with pair of combination I need
– franco_b
Nov 16 '18 at 14:34
add a comment |
0
0
0
I have this Postgres table with node of a directed graph:
node_id | node_sequence
-----------------------
1 1
2 2
3 3
I'd return a table with all the possible origin destination sequence (only in one direction) between the node :
(1,2);(1,2,3);(2,3) . So the output table should be:
node_id
----
1
2
1
2
3
2
3
maybe WITH RECURSIVE is the right thing to do but I cannot understand how.
thanks a lot
postgresql window-functions with-statement
asked Nov 16 '18 at 14:00
franco_bfranco_b
1861418
I have this Postgres table with node of a directed graph:
node_id | node_sequence
-----------------------
1 1
2 2
3 3
I'd return a table with all the possible origin destination sequence (only in one direction) between the node :
(1,2);(1,2,3);(2,3) . So the output table should be:
node_id
----
1
2
1
2
3
2
3
maybe WITH RECURSIVE is the right thing to do but I cannot understand how.
thanks a lot
postgresql window-functions with-statement
postgresql window-functions with-statement
asked Nov 16 '18 at 14:00
franco_bfranco_b
1861418
asked Nov 16 '18 at 14:00
franco_bfranco_b
1861418
edited Nov 16 '18 at 15:28
franco_b
asked Nov 16 '18 at 14:00
franco_bfranco_b
1861418
asked Nov 16 '18 at 14:00
franco_bfranco_b
1861418
asked Nov 16 '18 at 14:00
franco_bfranco_b
1861418
1861418
1
What are you defining as "combinations" ofa?
– Tim Biegeleisen
Nov 16 '18 at 14:06
I edit the question. I'd get all possible value combinations
– franco_b
Nov 16 '18 at 14:10
Why not {1,3} ? why not the sigletons {1}, {2}, {3} ?
– joop
Nov 16 '18 at 14:31
It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer was not clear. I just edit it with pair of combination I need
– franco_b
Nov 16 '18 at 14:34
add a comment |
1
What are you defining as "combinations" ofa?
– Tim Biegeleisen
Nov 16 '18 at 14:06
I edit the question. I'd get all possible value combinations
– franco_b
Nov 16 '18 at 14:10
Why not {1,3} ? why not the sigletons {1}, {2}, {3} ?
– joop
Nov 16 '18 at 14:31
It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer was not clear. I just edit it with pair of combination I need
– franco_b
Nov 16 '18 at 14:34
1
1
What are you defining as "combinations" of
a ?– Tim Biegeleisen
Nov 16 '18 at 14:06
What are you defining as "combinations" of
a ?– Tim Biegeleisen
Nov 16 '18 at 14:06
I edit the question. I'd get all possible value combinations
– franco_b
Nov 16 '18 at 14:10
I edit the question. I'd get all possible value combinations
– franco_b
Nov 16 '18 at 14:10
Why not {1,3} ? why not the sigletons {1}, {2}, {3} ?
– joop
Nov 16 '18 at 14:31
Why not {1,3} ? why not the sigletons {1}, {2}, {3} ?
– joop
Nov 16 '18 at 14:31
It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer was not clear. I just edit it with pair of combination I need
– franco_b
Nov 16 '18 at 14:34
It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer was not clear. I just edit it with pair of combination I need
– franco_b
Nov 16 '18 at 14:34
add a comment |
1 Answer
1
active
oldest
votes
1
Edit from initial answer:
You seem to have 2 constraints you do not mention in your question:
- You want sequences of at least 2 elements
- Elements in a sequence must be in ascending order and consecutive
Here is a simple query that does it (CTE GraphNode should be replaced with your table):
WITH RECURSIVE GraphPath AS (
SELECT G2.Node, ARRAY[G1.Node, G2.Node] AS GraphPath /* Start with 2 elements */
FROM GraphNode G1
JOIN GraphNode G2 ON G1.Node + 1 = G2.Node
UNION ALL
SELECT N.Node, P.GraphPath || N.Node
FROM GraphNode N
JOIN GraphPath P ON N.Node = 1 + P.Node
), GraphNode AS (
SELECT UNNEST(ARRAY[1,2,3]) AS Node
)
SELECT GraphPath
FROM GraphPath
ORDER BY GraphPath
answered Nov 16 '18 at 14:18
FXDFXD
1,536128
It' doesn't work. I don't want the "{1,3}" combination
– franco_b
Nov 16 '18 at 14:27
Can you detail the reason why {1,3} should be excluded? To me, it is a valid one (at least since you said "all possible combinations". Is it because 1 and 3 are not consecutive?
– FXD
Nov 16 '18 at 14:31
It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer is not clear. I just edit it with pair of combination I need
– franco_b
Nov 16 '18 at 14:33
Unless you can provide with another table where the graph edges are stored, there is nothing I can do. There is no operator for things that are only in your brain.
– FXD
Nov 16 '18 at 14:40
Thanks it works. But it's not generalizable query because graph edge is specific for this example. I also have sequence of 100 nodes.
– franco_b
Nov 16 '18 at 15:20
|
show 3 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53339331%2fpostgres-get-all-possible-origin-destination-node-sequence%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Edit from initial answer:
You seem to have 2 constraints you do not mention in your question:
- You want sequences of at least 2 elements
- Elements in a sequence must be in ascending order and consecutive
Here is a simple query that does it (CTE GraphNode should be replaced with your table):
WITH RECURSIVE GraphPath AS (
SELECT G2.Node, ARRAY[G1.Node, G2.Node] AS GraphPath /* Start with 2 elements */
FROM GraphNode G1
JOIN GraphNode G2 ON G1.Node + 1 = G2.Node
UNION ALL
SELECT N.Node, P.GraphPath || N.Node
FROM GraphNode N
JOIN GraphPath P ON N.Node = 1 + P.Node
), GraphNode AS (
SELECT UNNEST(ARRAY[1,2,3]) AS Node
)
SELECT GraphPath
FROM GraphPath
ORDER BY GraphPath
answered Nov 16 '18 at 14:18
FXDFXD
1,536128
It' doesn't work. I don't want the "{1,3}" combination
– franco_b
Nov 16 '18 at 14:27
Can you detail the reason why {1,3} should be excluded? To me, it is a valid one (at least since you said "all possible combinations". Is it because 1 and 3 are not consecutive?
– FXD
Nov 16 '18 at 14:31
It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer is not clear. I just edit it with pair of combination I need
– franco_b
Nov 16 '18 at 14:33
Unless you can provide with another table where the graph edges are stored, there is nothing I can do. There is no operator for things that are only in your brain.
– FXD
Nov 16 '18 at 14:40
Thanks it works. But it's not generalizable query because graph edge is specific for this example. I also have sequence of 100 nodes.
– franco_b
Nov 16 '18 at 15:20
|
show 3 more comments
1
Edit from initial answer:
You seem to have 2 constraints you do not mention in your question:
- You want sequences of at least 2 elements
- Elements in a sequence must be in ascending order and consecutive
Here is a simple query that does it (CTE GraphNode should be replaced with your table):
WITH RECURSIVE GraphPath AS (
SELECT G2.Node, ARRAY[G1.Node, G2.Node] AS GraphPath /* Start with 2 elements */
FROM GraphNode G1
JOIN GraphNode G2 ON G1.Node + 1 = G2.Node
UNION ALL
SELECT N.Node, P.GraphPath || N.Node
FROM GraphNode N
JOIN GraphPath P ON N.Node = 1 + P.Node
), GraphNode AS (
SELECT UNNEST(ARRAY[1,2,3]) AS Node
)
SELECT GraphPath
FROM GraphPath
ORDER BY GraphPath
answered Nov 16 '18 at 14:18
FXDFXD
1,536128
It' doesn't work. I don't want the "{1,3}" combination
– franco_b
Nov 16 '18 at 14:27
Can you detail the reason why {1,3} should be excluded? To me, it is a valid one (at least since you said "all possible combinations". Is it because 1 and 3 are not consecutive?
– FXD
Nov 16 '18 at 14:31
It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer is not clear. I just edit it with pair of combination I need
– franco_b
Nov 16 '18 at 14:33
Unless you can provide with another table where the graph edges are stored, there is nothing I can do. There is no operator for things that are only in your brain.
– FXD
Nov 16 '18 at 14:40
Thanks it works. But it's not generalizable query because graph edge is specific for this example. I also have sequence of 100 nodes.
– franco_b
Nov 16 '18 at 15:20
|
show 3 more comments
1
1
1
Edit from initial answer:
You seem to have 2 constraints you do not mention in your question:
- You want sequences of at least 2 elements
- Elements in a sequence must be in ascending order and consecutive
Here is a simple query that does it (CTE GraphNode should be replaced with your table):
WITH RECURSIVE GraphPath AS (
SELECT G2.Node, ARRAY[G1.Node, G2.Node] AS GraphPath /* Start with 2 elements */
FROM GraphNode G1
JOIN GraphNode G2 ON G1.Node + 1 = G2.Node
UNION ALL
SELECT N.Node, P.GraphPath || N.Node
FROM GraphNode N
JOIN GraphPath P ON N.Node = 1 + P.Node
), GraphNode AS (
SELECT UNNEST(ARRAY[1,2,3]) AS Node
)
SELECT GraphPath
FROM GraphPath
ORDER BY GraphPath
answered Nov 16 '18 at 14:18
FXDFXD
1,536128
Edit from initial answer:
You seem to have 2 constraints you do not mention in your question:
- You want sequences of at least 2 elements
- Elements in a sequence must be in ascending order and consecutive
Here is a simple query that does it (CTE GraphNode should be replaced with your table):
WITH RECURSIVE GraphPath AS (
SELECT G2.Node, ARRAY[G1.Node, G2.Node] AS GraphPath /* Start with 2 elements */
FROM GraphNode G1
JOIN GraphNode G2 ON G1.Node + 1 = G2.Node
UNION ALL
SELECT N.Node, P.GraphPath || N.Node
FROM GraphNode N
JOIN GraphPath P ON N.Node = 1 + P.Node
), GraphNode AS (
SELECT UNNEST(ARRAY[1,2,3]) AS Node
)
SELECT GraphPath
FROM GraphPath
ORDER BY GraphPath
answered Nov 16 '18 at 14:18
FXDFXD
1,536128
edited Nov 17 '18 at 3:55
answered Nov 16 '18 at 14:18
FXDFXD
1,536128
answered Nov 16 '18 at 14:18
FXDFXD
1,536128
answered Nov 16 '18 at 14:18
FXDFXD
1,536128
1,536128
It' doesn't work. I don't want the "{1,3}" combination
– franco_b
Nov 16 '18 at 14:27
Can you detail the reason why {1,3} should be excluded? To me, it is a valid one (at least since you said "all possible combinations". Is it because 1 and 3 are not consecutive?
– FXD
Nov 16 '18 at 14:31
It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer is not clear. I just edit it with pair of combination I need
– franco_b
Nov 16 '18 at 14:33
Unless you can provide with another table where the graph edges are stored, there is nothing I can do. There is no operator for things that are only in your brain.
– FXD
Nov 16 '18 at 14:40
Thanks it works. But it's not generalizable query because graph edge is specific for this example. I also have sequence of 100 nodes.
– franco_b
Nov 16 '18 at 15:20
|
show 3 more comments
It' doesn't work. I don't want the "{1,3}" combination
– franco_b
Nov 16 '18 at 14:27
Can you detail the reason why {1,3} should be excluded? To me, it is a valid one (at least since you said "all possible combinations". Is it because 1 and 3 are not consecutive?
– FXD
Nov 16 '18 at 14:31
It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer is not clear. I just edit it with pair of combination I need
– franco_b
Nov 16 '18 at 14:33
Unless you can provide with another table where the graph edges are stored, there is nothing I can do. There is no operator for things that are only in your brain.
– FXD
Nov 16 '18 at 14:40
Thanks it works. But it's not generalizable query because graph edge is specific for this example. I also have sequence of 100 nodes.
– franco_b
Nov 16 '18 at 15:20
It' doesn't work. I don't want the "{1,3}" combination
– franco_b
Nov 16 '18 at 14:27
It' doesn't work. I don't want the "{1,3}" combination
– franco_b
Nov 16 '18 at 14:27
Can you detail the reason why {1,3} should be excluded? To me, it is a valid one (at least since you said "all possible combinations". Is it because 1 and 3 are not consecutive?
– FXD
Nov 16 '18 at 14:31
Can you detail the reason why {1,3} should be excluded? To me, it is a valid one (at least since you said "all possible combinations". Is it because 1 and 3 are not consecutive?
– FXD
Nov 16 '18 at 14:31
It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer is not clear. I just edit it with pair of combination I need
– franco_b
Nov 16 '18 at 14:33
It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer is not clear. I just edit it with pair of combination I need
– franco_b
Nov 16 '18 at 14:33
Unless you can provide with another table where the graph edges are stored, there is nothing I can do. There is no operator for things that are only in your brain.
– FXD
Nov 16 '18 at 14:40
Unless you can provide with another table where the graph edges are stored, there is nothing I can do. There is no operator for things that are only in your brain.
– FXD
Nov 16 '18 at 14:40
Thanks it works. But it's not generalizable query because graph edge is specific for this example. I also have sequence of 100 nodes.
– franco_b
Nov 16 '18 at 15:20
Thanks it works. But it's not generalizable query because graph edge is specific for this example. I also have sequence of 100 nodes.
– franco_b
Nov 16 '18 at 15:20
|
show 3 more comments
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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.
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53339331%2fpostgres-get-all-possible-origin-destination-node-sequence%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
1
What are you defining as "combinations" of
a?– Tim Biegeleisen
Nov 16 '18 at 14:06
I edit the question. I'd get all possible value combinations
– franco_b
Nov 16 '18 at 14:10
Why not {1,3} ? why not the sigletons {1}, {2}, {3} ?
– joop
Nov 16 '18 at 14:31
It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer was not clear. I just edit it with pair of combination I need
– franco_b
Nov 16 '18 at 14:34