public inbox for pgsql-novice@postgresql.org  

help / color / mirror / Atom feed

From: Ibrahim Shaame <ishaame@gmail.com>
To: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
Subject: Adding column in a recursive query
Date: Mon, 30 Mar 2026 13:20:14 +0300
Message-ID: <CAJOWwD5_saumY5Xv1QAQ=VxwMQrz-tF6G-Fwek-ew52-=_uOXg@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-novice@postgresql.org
  Cc: ishaame@gmail.com, pgsql-novice@lists.postgresql.org
  Subject: Re: Adding column in a recursive query
  In-Reply-To: <CAJOWwD5_saumY5Xv1QAQ=VxwMQrz-tF6G-Fwek-ew52-=_uOXg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox