Express-Postgres fails to apply array filter: error: operator does not exist: integer[] && text[]

The same query works on the terminal. But for some reason I’m getting an error:

operator does not exist: integer[] && text[]

pg.query is unable to process the expression path && Array[$1] using this: https://www.postgresql.org/docs/9.2/functions-array.html

exports.getReplies = async function (commentID) {
  commentID = parseInt(commentID);
  console.log(commentID);
  return await db.query(
    `
      WITH RECURSIVE cte (comment_id, body, user_id, path, reply_to, depth)  AS (
          SELECT  comment_id,
              body,
              user_id,
              array[comment_id] AS path,
              reply_to,
              1 AS depth
          FROM    comments
          WHERE   reply_to IS NULL
      
          UNION ALL
      
          SELECT  comments.comment_id,
              comments.body,
              comments.user_id,
              cte.path || comments.comment_id,
              comments.reply_to,
              cte.depth + 1 AS depth
          FROM    comments
          JOIN cte ON comments.reply_to = cte.comment_id
          )
          SELECT comment_id, body, user_id, path, depth FROM cte
      WHERE path && Array[$1] AND comment_id != $2
      ORDER BY path;
      `,
    [commentID, commentID]
  );
};

Answer

This should work:

exports.getReplies = async function (commentID) {
  commentID = parseInt(commentID);
  console.log(commentID);
  return await db.query(
      `
      WITH RECURSIVE thread AS (
         SELECT comment_id, body, user_id
              , ARRAY[comment_id] AS path
              , reply_to
              , 1 AS depth
         FROM   comments
         WHERE  reply_to IS NULL

         UNION ALL
         SELECT c.comment_id, c.body, c.user_id
              , t.path || c.comment_id
              , c.reply_to
              , t.depth + 1 -- AS depth
         FROM   thread t
         JOIN   comments c ON c.reply_to = t.comment_id
         )
      SELECT comment_id, body, user_id, path, depth
      FROM   thread
      WHERE  $1::int = ANY(path)    -- !!!
      AND    comment_id <> $2::int
      ORDER  BY path;
      `,
    [commentID, commentID]
  );

Why?

Short answer: type resolution.

Long answer:

Concluding from the error message that comments.comment_id is of type integer. Forming an array from it with ARRAY[comment_id] produces an integer array: integer[], or int[] for short.

You want int[] or compatible on the other side, not text[]. Untyped string literals are passed (I assume, but the solution also works for parameters typed text or varchar). When fed to the ARRAY constructor in ARRAY[$1], type resolution is forced before a matching type can be deferred from the assignment in the expression. The default type is text, so you get text[]. Hence the error message:

error: operator does not exist: integer[] && text[] 

Explanation by example:

db<>fiddle here

You can fix this with an explicit cast, either before or after the ARRAY constructor is applied. But it’s slightly cheaper to do it first.

WHERE  path && ARRAY[$1::int]

Then again, my alternative, equivalent syntax is cheaper, yet:

WHERE  $1::int = ANY(path)

Also, if comments.comment_id can be NULL (which you also did not specify) or the recursive CTE adds a NULL to the array (the one at hand does not), your original expression with the ARRAY “overlap” operator && would fail as it cannot cope with NULL elements. So twice better.

The array operator would make sense to employ a GIN index on an array column. But a derived array column is never indexed, so there is no point in using the more expensive operator. See:

• Check if value exists in Postgres array

I added explicit casts to integer for both parameters. Does not hurt, but it’s not necessary in my version of the query – if parameters are passed as untyped string literals (which I assume).