Skip site navigation (1) Skip section navigation (2)

Re: Ambiguous error on view

From: "Toff" <kristoff(dot)six(at)telenet(dot)be>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Ambiguous error on view
Date: 2005-09-19 20:58:28
Message-ID: UDFXe.198217$H44.10413179@phobos.telenet-ops.be (view raw or flat)
Thread:
Lists: pgsql-novice
I found that
"
CREATE VIEW allops AS
    SELECT acc1.operator.operatorid, acc1.operator.accountid,
acc1.operator.name, acc2.operator.operatorid, acc2.operator.accountid,
acc2.operator.name
        FROM acc1.operator, acc2.operator
        WHERE (acc1.operator.active = true OR acc2.operator.active = true);
"
works for the ambiguous errors. But there are identical column names in the
view (operatorid, accountid, name, operatorid, accountid, name) witch 
obvious fires
an error "ERROR:  column "operatorid" duplicated"
So how to merge the data from the different schema's in one column in the 
view?

The global / symbolic statement of what I wnat to do is
SELECT operatorid FROM acc1.operator, acc2.operator,...... WHERE active = 
true;
Are there perhaps other ways to do this if the views don't work?

Greetz,

Kristoff


"Toff" <kristoff(dot)six(at)telenet(dot)be> wrote in message
news:XfwXe(dot)197826$L_7(dot)10628197(at)phobos(dot)telenet-ops(dot)be(dot)(dot)(dot)
> Hello!
>
> I want to create a view of a table that is in several schema's.
>
> The table is
> CREATE TABLE "operator"
> (
> "operatorid"  INTEGER   PRIMARY KEY,
> "accountid"  INTEGER   REFERENCES "account"("accountid"),
> "name"   VARCHAR (40),
> "active"  BOOLEAN,
> );
>
> The schema's are "acc1", "acc2", ......
>
> The view........
> CREATE VIEW allops AS
>    SELECT operatorid, accountid, name
>        FROM acc1.operator, acc2.operator
>        WHERE active = true;
>
> But I get the "ERROR:  column reference "operatorid" is ambiguous"
> If I remove "operatorid", or "accountid" in the view, the errortype is the
> same.
>
> What is the ambiguous thing about this? And how to solve it?
> On the internet I found some posts with the same issue, but no direct
> answers.
>
> Thanks,
>
> Kristoff
>




In response to

Responses

pgsql-novice by date

Next:From: Peter CookDate: 2005-09-20 02:15:35
Subject: How do I connect to postgreSQL via an application?
Previous:From: ToffDate: 2005-09-19 10:18:31
Subject: Ambiguous error on view

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group