postgresql - distance between two nodes-CodePudding

I am trying to figure out why this query is giving me problems. It is supposed to get the shortest path between nodes 4269 and 4215 (or any other two nodes) based on two tables. Table 1: flow_networks_networkedge (pkid, child_id, parent_id) and Table 2: flow_networks_networknode (pkid, name).

WITH RECURSIVE traverse(parent_id, child_id, depth, PATH) AS
    (SELECT first.parent_id,
            first.child_id,
            1 AS depth, ARRAY[first.parent_id] AS PATH
     FROM flow_networks_networkedge AS FIRST
     WHERE parent_id = 4269
     UNION ALL SELECT first.parent_id,
                      first.child_id,
                      second.depth   1 AS depth,
                      PATH || first.parent_id AS PATH
     FROM flow_networks_networkedge AS FIRST,
          traverse AS SECOND
     WHERE first.parent_id = second.child_id
         AND (first.parent_id <> ALL(second.path)) )
SELECT UNNEST(ARRAY[pkid]) AS pkid
FROM
    (SELECT PATH || ARRAY[4215], depth
     FROM traverse
     WHERE child_id = 4215
         AND depth <= 20
     LIMIT 1) AS x(pkid);






ERROR:  operator does not exist: bigint[] || integer[]
LINE 18:   (SELECT path || ARRAY[4215], depth
                        ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 628

CodePudding user response：

To concatenate the two arrays, they must be of the same type. You can cast the integer array to a bigint array.

SELECT path || ARRAY[4215]::bigint[]