Question 180966 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL – Aggregate attributes from different columns recursively

id_start id_end
1 2
2 3
3 4

I want to collect elements who are "connected" like [1, 2, 3, 4]. (in an array for example)

I tried a recursive query like:

WITH RECURSIVE q_rec AS (
    SELECT id_start,
           id_end
    FROM my_table
    UNION
    SELECT t.id_start
           t.id_end
    FROM       my_table t
    INNER JOIN q_rec r ON r.id_start = t.id_end
) 
SELECT *
FROM q_rec;

But how can I aggregate them in an array despite they are not in the same column ?

Tags:

2

Answers


  1. 0

    Try this, I’m assuming you know the start and end elements:

    WITH RECURSIVE r AS (
  SELECT id_start, id_end, ARRAY[id_start, id_end] AS chain
  FROM your_table
  WHERE id_start = 1

  UNION ALL

  SELECT your_table.id_start, your_table.id_end, r.chain || your_table.id_end
  FROM your_table JOIN r ON your_table.id_start = r.id_end
)
SELECT chain FROM r WHERE id_end = 4;
    Login or Signup to reply.
  2. 0

    Another approach gathers all ids in the recursive query, as a table. Then applies aggregation.

    WITH RECURSIVE cte AS (
  SELECT id_start AS first_id, 
         id_start AS id
  FROM tab t1
  WHERE NOT EXISTS(SELECT 1 FROM tab t2 WHERE t1.id_start = t2.id_end)

  UNION ALL

  SELECT cte.first_id,
         tab.id_end
  FROM       cte
  INNER JOIN tab
          ON cte.id = tab.id_start
)
SELECT first_id, 
       ARRAY_AGG(id) AS ids
FROM cte
GROUP BY first_id

    Output:

    first_id ids
    1 [1,2,3,4]

    Check the demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search