Inserting values into array in PostgreSQL

Question

I am trying to insert values into an integer array, used as path to show all ancestors of a particular node.

These values (parent_link integer) are from a table with ID and parent_link. I am trying to traverse the tree-like structure to assemble all parent_link in a path to insert into an integer array belonging to that particular ID. I am trying to do this for every single record in my database. So far I have:

INSERT INTO master_ifis_network (path)
SELECT t2.parent_link
FROM master_ifis_network as t2
WHERE t2.parent_link = (SELECT t1.parent_link
     FROM master_ifis_network as t1)
AND t2.link_id = (parent_link)

I get an error saying that I cannot insert an integer where an integer[] is expected.
I have also tried this, which outputs a list of the parent nodes:

SELECT parentX.parent_link FROM [table name] as nodeX, [table name] as parentx
WHERE nodeX.left BETWEEN parentX.left AND parentX.right)
AND nodeX.link_id = [some id] 
ORDER BY parentX.left DESC

Any hints or ideas?

Answer 1

Use a recursive CTE, i.e.: WITH RECURSIVE.
And you need an UPDATE, not an INSERT:

WITH RECURSIVE cte AS (
   SELECT link_id, ARRAY[parent_link] AS path, 1 AS level
   FROM   master_ifis_network

   UNION  ALL
   SELECT c.link_id, m.parent_link || c.path, c.level + 1
   FROM   cte c
   JOIN   master_ifis_network m ON m.link_id = c.path[1]
   )
UPDATE master_ifis_network m
SET    path = sub.path
FROM  (
   SELECT DISTINCT ON (link_id) *
   FROM   cte
   ORDER  BY link_id, level DESC
  ) sub
WHERE m.link_id = sub.link_id;

Related answers:

• Tree Structure and Recursion
• Recursive query used for transitive closure

There are many others here. Search with above key words.

Answer 2

Here is what I ended up with:

UPDATE master_ifis_network SET path = array(SELECT parentX.parent_link 
FROM master_ifis_network AS parentX 
WHERE (nodeX.left BETWEEN parentX.left AND parentX.right) AND nodeX.link_id = [some id] 
ORDER BY parentX.left DESC) 
WHERE link_id = [same id];"