Re: Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: David Johnston <polobo(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2
Date: 2013-09-12 00:07:36
Message-ID: 523105C8.8020500@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I took your sample and modified it a bit.

ERROR: table name "tblb" specified more than once
Switch tblB and tblC in the subquery and it works.

WITH
tblA (a_id, a_val) AS ( VALUES (1::integer, 'one-1'::varchar) )
, tblB (b_id, b_val) AS ( VALUES (1::integer, 'one-b'::varchar) )
, tblC (c_id, c_val) AS ( VALUES (1::integer, 'one-c'::varchar) )

select *
from tblA
join tblB on tblA.a_id = tblB.b_id
join (
tblB
join tblC on tblC.c_id = tblB.b_id
) as x on tblA.a_id = x.c_id;

Am 12.09.2013 00:52, schrieb David Johnston:
> Andreas-3-2 wrote
>> I never placed a bug-report. :}
>> Could you give a hand?
> Sure.
>
> PostgreSQL homepage (postgresql.org)
> "Report a Bug" link on right-hand side of page.
> Fill in the form.
>
>>>> SEND THIS>>>>
>
>> drop table if exists sub_tab;
>> drop table if exists main_tab;
>> drop table if exists flag_1;
>> drop table if exists flag_2;
>>
>> create temporary table flag_1 ( flag_1_id integer primary key, flag_1_t
>> text );
>> insert into flag_1 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );
>>
>> create temporary table flag_2 ( flag_2_id integer primary key, flag_2_t
>> text );
>> insert into flag_2 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );
>>
>> create temporary table main_tab ( main_id integer primary key, main_t
>> text, flag_1_id integer references flag_1 ( flag_1_id ) );
>> insert into main_tab values ( 1, 'Main 1', 1 ), ( 2, 'Main 2', 2 ), ( 3,
>> 'Main 3', 3 );
>>
>> create temporary table sub_tab ( sub_id integer primary key, sub_t
>> text, main_id integer references main_tab ( main_id ), flag_1_id integer
>> references flag_1 ( flag_1_id ), flag_2_id integer references flag_2 (
>> flag_2_id ) );
>> insert into sub_tab values ( 1, 'Sub 1', 1, 3, 1 ), ( 2, 'Sub 2', 2, 2,
>> 2 ), ( 3, 'Sub 3', 3, 1, 3 );
>>
>>
>> select m.main_id, m.main_t, f.flag_1_t,
>> x.sub_id, x.sub_t, x.flag_1_t, x.flag_2_t
>> from main_tab as m
>> join flag_1 as f using ( flag_1_id )
>> left join
>> (
>> sub_tab as s
>> join flag_2 as f2 using ( flag_2_id ) -- that
>> way it works
>> join flag_1 as f using ( flag_1_id )
>> -- join flag_2 as f2 using ( flag_2_id ) -- that
>> way it doesn't work
>> ) as x using ( main_id );
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770534.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message James Sewell 2013-09-12 00:25:02 Re: pg_largeobjects
Previous Message Gregory Haase 2013-09-12 00:00:36 Re: ZFS snapshots - to use pg_start_backup() and pg_stop_backup() or no?