Обсуждение: Adding column in a recursive query
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;
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.
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
On Mon, Mar 30, 2026 at 01:20:14PM +0300, Ibrahim Shaame wrote: > I have a working recursive query. I want to add another column, but it > gives me an 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? You can't compare text and integer. Does 'abc' equal 0 ? What about '01' and 1 ? Cast one side to the type of the other. Or, better yet, normalize datatypes in tables, so that you don't have to compare across types. Best regards, depesz
Hello Hubert.
Thanks for the reply. Both are integers and they work well without the two lines. So what changed one of them to text. Can you see where? I have not been able to identify.
Regards
Ibrahim
On Mon, Mar 30, 2026 at 3:16 PM hubert depesz lubaczewski <depesz@depesz.com> wrote:
On Mon, Mar 30, 2026 at 01:20:14PM +0300, Ibrahim Shaame wrote:
> I have a working recursive query. I want to add another column, but it
> gives me an 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?
You can't compare text and integer.
Does 'abc' equal 0 ?
What about '01' and 1 ?
Cast one side to the type of the other. Or, better yet, normalize
datatypes in tables, so that you don't have to compare across types.
Best regards,
depesz
Ibrahim Shaame <ishaame@gmail.com> writes:
> Thanks for the reply. Both are integers and they work well without the two
> lines. So what changed one of them to text. Can you see where? I have not
> been able to identify.
This bit is forcing the column names for just the first three
output columns, leaving the rest to default from the SELECT
targetlist:
WITH RECURSIVE x(jina, namba, nasaba_1) AS (
That's a hazardous practice: usually I'd force all or none of
the column names that way. In this case, I speculate that you
carelessly added the new column as one of the physically first
three SELECT outputs, and didn't adjust this list to match,
leading to confusion about which column is "x.namba".
If that's not it, you need to be a great deal more specific
about exactly how you changed the query.
regards, tom lane
On Mon, Mar 30, 2026 at 05:22:53PM +0300, Ibrahim Shaame wrote: > Hello Hubert. > Thanks for the reply. Both are integers and they work well without the two > lines. So what changed one of them to text. Can you see where? I have not > been able to identify. Please show *exact* query that fails, and `\d` of table(s) that are being used. Best regards, depesz
Effectively, after removing the column names from x(jina, namba, nasaba_1) it works now. Thank you very much. But then I don't understand the advantage or inconvenience of naming or not naming the columns there. Is there any explanation somewhere?
Thanks again
Ibrahim
On Mon, Mar 30, 2026 at 5:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ibrahim Shaame <ishaame@gmail.com> writes:
> Thanks for the reply. Both are integers and they work well without the two
> lines. So what changed one of them to text. Can you see where? I have not
> been able to identify.
This bit is forcing the column names for just the first three
output columns, leaving the rest to default from the SELECT
targetlist:
WITH RECURSIVE x(jina, namba, nasaba_1) AS (
That's a hazardous practice: usually I'd force all or none of
the column names that way. In this case, I speculate that you
carelessly added the new column as one of the physically first
three SELECT outputs, and didn't adjust this list to match,
leading to confusion about which column is "x.namba".
If that's not it, you need to be a great deal more specific
about exactly how you changed the query.
regards, tom lane
On Monday, March 30, 2026, Ibrahim Shaame <ishaame@gmail.com> wrote:
Effectively, after removing the column names from x(jina, namba, nasaba_1) it works now. Thank you very much. But then I don't understand the advantage or inconvenience of naming or not naming the columns there. Is there any explanation somewhere?
SQL is big on providing ways to give aliases/names to things. It just boils down to readability or, more often, conventions, as to which ones to use. The CTE column names clause doesn’t get used much that I have seen. You have to write a full query inside the CTE anyway so column aliases are more conventional.
David J.
Thank you David for clarifying
.
Ibrahim Shaame
On Mon, Mar 30, 2026 at 6:18 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Monday, March 30, 2026, Ibrahim Shaame <ishaame@gmail.com> wrote:Effectively, after removing the column names from x(jina, namba, nasaba_1) it works now. Thank you very much. But then I don't understand the advantage or inconvenience of naming or not naming the columns there. Is there any explanation somewhere?SQL is big on providing ways to give aliases/names to things. It just boils down to readability or, more often, conventions, as to which ones to use. The CTE column names clause doesn’t get used much that I have seen. You have to write a full query inside the CTE anyway so column aliases are more conventional.David J.