Adding column in a recursive query

From: Ibrahim Shaame <ishaame(at)gmail(dot)com>
To: "pgsql-novice(at)lists(dot)postgresql(dot)org" <pgsql-novice(at)lists(dot)postgresql(dot)org>
Subject: Adding column in a recursive query
Date: 2026-03-30 10:20:14
Message-ID: CAJOWwD5_saumY5Xv1QAQ=VxwMQrz-tF6G-Fwek-ew52-=_uOXg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message hubert depesz lubaczewski 2026-03-30 12:16:15 Re: Adding column in a recursive query
Previous Message Laurenz Albe 2026-03-26 17:17:23 Re: AW: AW: Very basic question about Archive logs