Horton

Thread

  1. Adding column in a recursive query

    Ibrahim Shaame <ishaame@gmail.com> — 2026-03-30T10:20:14Z

    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

  2. Re: Adding column in a recursive query

    hubert depesz lubaczewski <depesz@depesz.com> — 2026-03-30T12:16:15Z

    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

  3. Re: Adding column in a recursive query

    Ibrahim Shaame <ishaame@gmail.com> — 2026-03-30T14:22:53Z

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

  4. Re: Adding column in a recursive query

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-03-30T14:31:18Z

    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

  5. Re: Adding column in a recursive query

    hubert depesz lubaczewski <depesz@depesz.com> — 2026-03-30T14:52:33Z

    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

  6. Re: Adding column in a recursive query

    Ibrahim Shaame <ishaame@gmail.com> — 2026-03-30T15:11:08Z

    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
>

  7. Re: Adding column in a recursive query

    David G. Johnston <david.g.johnston@gmail.com> — 2026-03-30T15:18:09Z

    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.

  8. Re: Adding column in a recursive query

    Ibrahim Shaame <ishaame@gmail.com> — 2026-04-05T10:01:09Z

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