| 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
| 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 |