MIME-Version: 1.0
From: Ibrahim Shaame <ishaame@gmail.com>
Date: Mon, 30 Mar 2026 13:20:14 +0300
Message-ID: 
 <CAJOWwD5_saumY5Xv1QAQ=VxwMQrz-tF6G-Fwek-ew52-=_uOXg@mail.gmail.com>
Subject: Adding column in a recursive query
To: "pgsql-novice@lists.postgresql.org" <pgsql-novice@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000410a57064e3b330f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJOWwD5_saumY5Xv1QAQ%3DVxwMQrz-tF6G-Fwek-ew52-%3D_uOXg%40mail.gmail.com>
Precedence: bulk

--000000000000410a57064e3b330f
Content-Type: text/plain; charset="UTF-8"

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

--000000000000410a57064e3b330f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>I have a working recursive query. I want to add anoth=
er column, but it gives me an error:=C2=A0</div><div>Here is the working on=
e:</div><div>*****************************</div><div>WITH RECURSIVE x(jina,=
 namba, nasaba_1) AS (<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT (((majin=
a2.jina::text || &#39; &#39;::text) || majina2.baba::text) || &#39; &#39;::=
text) || majina2.babu::text AS jina,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 majina2.namba,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 majina2.=
nasaba_1,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 majina2.kuzaliwa,<br=
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 majina2.anza_mchango,<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 majina2.mwisho_mchango<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0FROM majina2<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 WHERE majina2.nasaba_1 =3D 0<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 UNION ALL<b=
r>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT ((((((x_1.jina || &#39; &#39;::t=
ext) || &#39; - &#39;::text) || e.jina::text) || &#39; &#39;::text) || e.ba=
ba::text) || &#39; &#39;::text) || e.babu::text,<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 e.namba,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
e.nasaba_1,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 e.kuzaliwa,<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 e.anza_mchango,<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 e.mwisho_mchango<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0FROM majina2 e,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 x=
 x_1<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE e.nasaba_1 =3D x_1.namba<b=
r>=C2=A0 =C2=A0 =C2=A0 =C2=A0 )<br>=C2=A0SELECT jina,<br>=C2=A0 =C2=A0 namb=
a,<br>=C2=A0 =C2=A0 nasaba_1,<br>=C2=A0 =C2=A0 (length(jina) - length(repla=
ce(jina, &#39;-&#39;::text, &#39;&#39;::text))) / length(&#39;-&#39;::text)=
 AS depth,<br>=C2=A0 =C2=A0 kuzaliwa,<br>=C2=A0 =C2=A0 anza_mchango,<br>=C2=
=A0 =C2=A0 mwisho_mchango<br>=C2=A0 =C2=A0FROM x<br>=C2=A0 ORDER BY jina;</=
div><div>************************</div><div>But when I add these lines:</di=
v><div>=C2=A0 =C2=A0(((majina2.jina::text || &#39; &#39;::text) || majina2.=
baba::text) || &#39; &#39;::text) || majina2.babu::text AS jina_2,</div><di=
v>the upper part when executed separately its works:=C2=A0 <br></div><div><=
br></div><div>When adding this in the UNION part:=C2=A0=C2=A0(((e.jina::tex=
t || &#39; &#39;::text) || e.baba::text) || &#39; &#39;::text) || e.babu::t=
ext AS jina_2,</div><div>I get the following error:</div><div><br></div><di=
v>ERROR: =C2=A0operator does not exist: integer =3D text<br>LINE 21: =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE e.nasaba_1 =3D x_1.namba<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^<br>HINT: =C2=A0No operator matc=
hes the given name and argument types. You might need to add explicit type =
casts.</div><div><br></div><div><br></div><div>Any suggestion of where I am=
 doing it wrong?=C2=A0</div><div><br></div><div>Thanks in advance</div><div=
>Ibrahim Shaame</div></div>

--000000000000410a57064e3b330f--