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?

I'm not getting how SQL Server will expect "integer[]" value to insert into a field. Am I missing something? http://msdn.microsoft.com/en-us/library/ms187752.aspx — celerno, Jul 23 '14 at 16:50
What is the exact error message and the `master_ifis_network` schema? — mu is too short, Jul 23 '14 at 18:05
A question like this *needs* a table definition (what you get with `\d tbl` in `psql`) and the exact version of Postgres. — Erwin Brandstetter, Jul 23 '14 at 18:37
@celerno [tag:sql] doesn't means [tag:ms-sql-server], it means *"the structured query language, SQL"*. Please don't buy into the confusion Microsoft created with their confusing product naming by assuming that "SQL" means "MS SQL Server". — Craig Ringer, Jul 24 '14 at 02:02
Question didn't include tags and comments when i asked. Thanks. — celerno, Jul 24 '14 at 03:20

score 2 · Answer 1 · edited May 23 '17 at 11:50

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;

Inserting values into array in PostgreSQL

2 Answers2