public inbox for pgsql-novice@postgresql.org
help / color / mirror / Atom feedAdding column in a recursive query
8+ messages / 4 participants
[nested] [flat]
* Adding column in a recursive query
@ 2026-03-30 10:20 Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 12:16 ` Re: Adding column in a recursive query hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ibrahim Shaame @ 2026-03-30 10:20 UTC (permalink / raw)
To: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Adding column in a recursive query
2026-03-30 10:20 Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
@ 2026-03-30 12:16 ` hubert depesz lubaczewski <depesz@depesz.com>
2026-03-30 14:22 ` Re: Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: hubert depesz lubaczewski @ 2026-03-30 12:16 UTC (permalink / raw)
To: Ibrahim Shaame <ishaame@gmail.com>; +Cc: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Adding column in a recursive query
2026-03-30 10:20 Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 12:16 ` Re: Adding column in a recursive query hubert depesz lubaczewski <depesz@depesz.com>
@ 2026-03-30 14:22 ` Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 14:31 ` Re: Adding column in a recursive query Tom Lane <tgl@sss.pgh.pa.us>
2026-03-30 14:52 ` Re: Adding column in a recursive query hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 8+ messages in thread
From: Ibrahim Shaame @ 2026-03-30 14:22 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Adding column in a recursive query
2026-03-30 10:20 Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 12:16 ` Re: Adding column in a recursive query hubert depesz lubaczewski <depesz@depesz.com>
2026-03-30 14:22 ` Re: Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
@ 2026-03-30 14:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-03-30 15:11 ` Re: Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2026-03-30 14:31 UTC (permalink / raw)
To: Ibrahim Shaame <ishaame@gmail.com>; +Cc: depesz@depesz.com; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Adding column in a recursive query
2026-03-30 10:20 Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 12:16 ` Re: Adding column in a recursive query hubert depesz lubaczewski <depesz@depesz.com>
2026-03-30 14:22 ` Re: Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 14:31 ` Re: Adding column in a recursive query Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-03-30 15:11 ` Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 15:18 ` Re: Adding column in a recursive query David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ibrahim Shaame @ 2026-03-30 15:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: depesz@depesz.com; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Adding column in a recursive query
2026-03-30 10:20 Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 12:16 ` Re: Adding column in a recursive query hubert depesz lubaczewski <depesz@depesz.com>
2026-03-30 14:22 ` Re: Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 14:31 ` Re: Adding column in a recursive query Tom Lane <tgl@sss.pgh.pa.us>
2026-03-30 15:11 ` Re: Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
@ 2026-03-30 15:18 ` David G. Johnston <david.g.johnston@gmail.com>
2026-04-05 10:01 ` Re: Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2026-03-30 15:18 UTC (permalink / raw)
To: Ibrahim Shaame <ishaame@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; depesz@depesz.com <depesz@depesz.com>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Adding column in a recursive query
2026-03-30 10:20 Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 12:16 ` Re: Adding column in a recursive query hubert depesz lubaczewski <depesz@depesz.com>
2026-03-30 14:22 ` Re: Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 14:31 ` Re: Adding column in a recursive query Tom Lane <tgl@sss.pgh.pa.us>
2026-03-30 15:11 ` Re: Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 15:18 ` Re: Adding column in a recursive query David G. Johnston <david.g.johnston@gmail.com>
@ 2026-04-05 10:01 ` Ibrahim Shaame <ishaame@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Ibrahim Shaame @ 2026-04-05 10:01 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; depesz@depesz.com <depesz@depesz.com>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Adding column in a recursive query
2026-03-30 10:20 Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 12:16 ` Re: Adding column in a recursive query hubert depesz lubaczewski <depesz@depesz.com>
2026-03-30 14:22 ` Re: Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
@ 2026-03-30 14:52 ` hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 0 replies; 8+ messages in thread
From: hubert depesz lubaczewski @ 2026-03-30 14:52 UTC (permalink / raw)
To: Ibrahim Shaame <ishaame@gmail.com>; +Cc: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-04-05 10:01 UTC | newest]
Thread overview: 8+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-30 10:20 Adding column in a recursive query Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 12:16 ` hubert depesz lubaczewski <depesz@depesz.com>
2026-03-30 14:22 ` Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 14:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-03-30 15:11 ` Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 15:18 ` David G. Johnston <david.g.johnston@gmail.com>
2026-04-05 10:01 ` Ibrahim Shaame <ishaame@gmail.com>
2026-03-30 14:52 ` hubert depesz lubaczewski <depesz@depesz.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox