Lists: | pgsql-novice |
---|
From: | "Toff" <kristoff(dot)six(at)telenet(dot)be> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Ambiguous error on view |
Date: | 2005-09-19 10:18:31 |
Message-ID: | XfwXe.197826$L_7.10628197@phobos.telenet-ops.be |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
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
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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
>
From: | Gnanavel S <s(dot)gnanavel(at)gmail(dot)com> |
---|---|
To: | Toff <kristoff(dot)six(at)telenet(dot)be> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Ambiguous error on view |
Date: | 2005-09-23 06:18:11 |
Message-ID: | eec3b03c05092223185609251@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
On 9/20/05, Toff <kristoff(dot)six(at)telenet(dot)be> wrote:
>
> I found that
> "
> CREATE VIEW allops AS
> SELECT acc1.operator.operatorid, acc1.operator.accountid,
> acc1.operator.name <http://acc1.operator.name>, acc2.operator.operatorid,
> acc2.operator.accountid,
> acc2.operator.name <http://acc2.operator.name>
> FROM acc1.operator, acc2.operator
> WHERE (acc1.operator.active = true OR acc2.operator.active = true);
> "
try this,
CREATE VIEW allops AS
select operatorid,operatorname from acc1.operator where active=true
union
select operatorid,operatorname from acc2.operator where 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
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Gnanavel S <s(dot)gnanavel(at)gmail(dot)com> |
Cc: | Toff <kristoff(dot)six(at)telenet(dot)be>, pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Ambiguous error on view |
Date: | 2005-09-23 13:43:10 |
Message-ID: | 3219.1127482990@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
Gnanavel S <s(dot)gnanavel(at)gmail(dot)com> writes:
> try this,
> CREATE VIEW allops AS
> select operatorid,operatorname from acc1.operator where active=3Dtrue
> union
> select operatorid,operatorname from acc2.operator where active=3Dtrue
> ;
Most likely you want UNION ALL not just UNION.
regards, tom lane