Adding column in a recursive query

Ibrahim Shaame <ishaame@gmail.com>

From: Ibrahim Shaame <ishaame@gmail.com>
To: "pgsql-novice@lists.postgresql.org" <pgsql-novice@lists.postgresql.org>
Date: 2026-03-30T10:20:14Z
Lists: pgsql-novice
I have a working recursive query. I want to add another column, but it
gives me an error:
Here is the working one:
*****************************
WITH RECURSIVE x(jina, namba, nasaba_1) AS (
         SELECT (((majina2.jina::text || ' '::text) || majina2.baba::text)
|| ' '::text) || majina2.babu::text AS jina,
            majina2.namba,
            majina2.nasaba_1,
            majina2.kuzaliwa,
            majina2.anza_mchango,
            majina2.mwisho_mchango
           FROM majina2
          WHERE majina2.nasaba_1 = 0
        UNION ALL
         SELECT ((((((x_1.jina || ' '::text) || ' - '::text) ||
e.jina::text) || ' '::text) || e.baba::text) || ' '::text) || e.babu::text,
            e.namba,
            e.nasaba_1,
            e.kuzaliwa,
            e.anza_mchango,
            e.mwisho_mchango
           FROM majina2 e,
            x x_1
          WHERE e.nasaba_1 = x_1.namba
        )
 SELECT jina,
    namba,
    nasaba_1,
    (length(jina) - length(replace(jina, '-'::text, ''::text))) /
length('-'::text) AS depth,
    kuzaliwa,
    anza_mchango,
    mwisho_mchango
   FROM x
  ORDER BY jina;
************************
But when I add these lines:
   (((majina2.jina::text || ' '::text) || majina2.baba::text) || ' '::text)
|| majina2.babu::text AS jina_2,
the upper part when executed separately its works:

When adding this in the UNION part:  (((e.jina::text || ' '::text) ||
e.baba::text) || ' '::text) || e.babu::text AS jina_2,
I get the following error:

ERROR:  operator does not exist: integer = text
LINE 21:           WHERE e.nasaba_1 = x_1.namba
                                    ^
HINT:  No operator matches the given name and argument types. You might
need to add explicit type casts.


Any suggestion of where I am doing it wrong?

Thanks in advance
Ibrahim Shaame