Find shortest and longest path with recursive postgreSql

Question

I am new to PostgreSQL and in recursive queries and have this following problem: I have the following tables

node(id, colour)
edge(n1,n2)

I have to write a query with a recursive statement that returns the shortest and longest paths between each pair of nodes. The output should look like this: (id1,id2, shortpath,longpath)

Please provide sample data and desired results. Note that in a generic graph, there is no "longest" path because you can just keep looping around. Also, SQL is not the best tool for this, so it won't be the most efficient way to accomplish this. — Gordon Linoff
– Gordon Linoff, Commented Jun 18, 2021 at 15:10
The graph is not endless. The nodes go one way for example (A goes to B) and not backwards(B to A). Sample data: (A,B) (A,C) (B,D) (D,C) (C,E) And the results should look like : (A,E,2,4) etc — Reacting Innocent
– Reacting Innocent, Commented Jun 18, 2021 at 15:13

The Impaler · Accepted Answer · 2021-06-18 18:30:59Z

3

SQL is probably not the best tool to do this, but you can do it using a recursive CTE.

For example:

with recursive
p as (
  select n1, n2 as n, '/' || n1 || '/' as path, 1 as cost from edge where n1 = 'A'
 union all
  select p.n1, e.n2, p.path || e.n2 || '/', cost + 1
  from p
  join edge e on e.n1 = p.n
  where p.path not like '/' || e.n1 || '/'
    and p.n <> 'E'
)
select 'A' as source, 'E' as target, 
  (select min(cost) from p where n = 'E'),
  (select max(cost) from p where n = 'E');

Result:

source  target  min  max 
------- ------- ---- --- 
A       E       2    4

See running example at DB Fiddle.

answered Jun 18, 2021 at 18:30

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Find shortest and longest path with recursive postgreSql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related