agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedERROR: column "gid" specified more than once
8+ messages / 4 participants
[nested] [flat]
* ERROR: column "gid" specified more than once
@ 2015-05-12 08:31 BahramPSQL <bahramjomezade.gis92@gmail.com>
2015-05-12 15:07 ` Re: ERROR: column "gid" specified more than once David G. Johnston <david.g.johnston@gmail.com>
2015-05-12 15:26 ` Re: ERROR: column "gid" specified more than once Jason Aleski <jason.aleski@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: BahramPSQL @ 2015-05-12 08:31 UTC (permalink / raw)
To: pgsql-sql
I want to run a spatial query from two table!
CREATE TABLE "BorujerdDistCent" as
SELECT
*, st_distance(st_centroid("Lorestan".geometry),"Borujerd".geometry)/1000
as DistFromCntroid
FROM "Borujerd", "Lorestan"
Where "Lorestan"."Shahrestan" = 'بروجرد'
ORDER BY
"Borujerd".abady_name ASC
But i will see this error!
ERROR: column "gid" specified more than once
********** Error **********
ERROR: column "gid" specified more than once
SQL state: 42701
--
View this message in context: http://postgresql.nabble.com/ERROR-column-gid-specified-more-than-once-tp5848845.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ERROR: column "gid" specified more than once
2015-05-12 08:31 ERROR: column "gid" specified more than once BahramPSQL <bahramjomezade.gis92@gmail.com>
@ 2015-05-12 15:07 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2015-05-12 15:07 UTC (permalink / raw)
To: BahramPSQL <bahramjomezade.gis92@gmail.com>; +Cc: pgsql-sql
On Tuesday, May 12, 2015, BahramPSQL <bahramjomezade.gis92@gmail.com> wrote:
> I want to run a spatial query from two table!
>
> CREATE TABLE "BorujerdDistCent" as
> SELECT
> *, st_distance(st_centroid("Lorestan".geometry),"Borujerd".geometry)/1000
> as DistFromCntroid
> FROM "Borujerd", "Lorestan"
> Where "Lorestan"."Shahrestan" = 'بروجرد'
> ORDER BY
> "Borujerd".abady_name ASC
>
> But i will see this error!
> ERROR: column "gid" specified more than once
> ********** Error **********
>
> ERROR: column "gid" specified more than once
> SQL state: 42701
>
>
This is not a bug. You need to rewrite the query to avoid two output
columns named "gid".
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ERROR: column "gid" specified more than once
2015-05-12 08:31 ERROR: column "gid" specified more than once BahramPSQL <bahramjomezade.gis92@gmail.com>
@ 2015-05-12 15:26 ` Jason Aleski <jason.aleski@gmail.com>
2015-05-12 15:49 ` Re: ERROR: column "gid" specified more than once David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Jason Aleski @ 2015-05-12 15:26 UTC (permalink / raw)
To: pgsql-sql
You probably need to specify your wildcard on both tables.
CREATE TABLE "BorujerdDistCent" as
SELECT
"Borujerd".*, "Lorestan".*, t_distance(st_centroid("Lorestan".geometry),"Borujerd".geometry)/1000
as DistFromCntroid
FROM "Borujerd", "Lorestan"
Where "Lorestan"."Shahrestan" = 'بروجرد'
ORDER BY
"Borujerd".abady_name ASC
Jason Aleski / IT Specialist
On 5/12/2015 3:31 AM, BahramPSQL wrote:
> I want to run a spatial query from two table!
>
> CREATE TABLE "BorujerdDistCent" as
> SELECT
> *, st_distance(st_centroid("Lorestan".geometry),"Borujerd".geometry)/1000
> as DistFromCntroid
> FROM "Borujerd", "Lorestan"
> Where "Lorestan"."Shahrestan" = 'بروجرد'
> ORDER BY
> "Borujerd".abady_name ASC
>
> But i will see this error!
> ERROR: column "gid" specified more than once
> ********** Error **********
>
> ERROR: column "gid" specified more than once
> SQL state: 42701
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/ERROR-column-gid-specified-more-than-once-tp5848845.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ERROR: column "gid" specified more than once
2015-05-12 08:31 ERROR: column "gid" specified more than once BahramPSQL <bahramjomezade.gis92@gmail.com>
2015-05-12 15:26 ` Re: ERROR: column "gid" specified more than once Jason Aleski <jason.aleski@gmail.com>
@ 2015-05-12 15:49 ` David G. Johnston <david.g.johnston@gmail.com>
2015-05-12 15:53 ` Re: ERROR: column "gid" specified more than once ktm@rice.edu <ktm@rice.edu>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2015-05-12 15:49 UTC (permalink / raw)
To: Jason Aleski <jason.aleski@gmail.com>; +Cc: pgsql-sql
On Tuesday, May 12, 2015, Jason Aleski <jason.aleski@gmail.com> wrote:
> You probably need to specify your wildcard on both tables.
>
> CREATE TABLE "BorujerdDistCent" as
> SELECT
> "Borujerd".*, "Lorestan".*,
> t_distance(st_centroid("Lorestan".geometry),"Borujerd".geometry)/1000
> as DistFromCntroid
> FROM "Borujerd", "Lorestan"
>
>
My bad on the assumed -bugs list from before...
Anyway, how is this suugestion different from simply saying "*" without a
relation specification - which the OP did and it didn't work.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ERROR: column "gid" specified more than once
2015-05-12 08:31 ERROR: column "gid" specified more than once BahramPSQL <bahramjomezade.gis92@gmail.com>
2015-05-12 15:26 ` Re: ERROR: column "gid" specified more than once Jason Aleski <jason.aleski@gmail.com>
2015-05-12 15:49 ` Re: ERROR: column "gid" specified more than once David G. Johnston <david.g.johnston@gmail.com>
@ 2015-05-12 15:53 ` ktm@rice.edu <ktm@rice.edu>
2015-05-12 16:09 ` Re: ERROR: column "gid" specified more than once David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: ktm@rice.edu @ 2015-05-12 15:53 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Jason Aleski <jason.aleski@gmail.com>; pgsql-sql
On Tue, May 12, 2015 at 08:49:53AM -0700, David G. Johnston wrote:
> On Tuesday, May 12, 2015, Jason Aleski <jason.aleski@gmail.com> wrote:
>
> > You probably need to specify your wildcard on both tables.
> >
> > CREATE TABLE "BorujerdDistCent" as
> > SELECT
> > "Borujerd".*, "Lorestan".*,
> > t_distance(st_centroid("Lorestan".geometry),"Borujerd".geometry)/1000
> > as DistFromCntroid
> > FROM "Borujerd", "Lorestan"
> >
> >
> My bad on the assumed -bugs list from before...
>
> Anyway, how is this suugestion different from simply saying "*" without a
> relation specification - which the OP did and it didn't work.
>
> David J.
Because the column names are differentiated by their prefixes then:
Borujerd.gid, Lorestan.gid
No conflict.
Regards,
Ken
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ERROR: column "gid" specified more than once
2015-05-12 08:31 ERROR: column "gid" specified more than once BahramPSQL <bahramjomezade.gis92@gmail.com>
2015-05-12 15:26 ` Re: ERROR: column "gid" specified more than once Jason Aleski <jason.aleski@gmail.com>
2015-05-12 15:49 ` Re: ERROR: column "gid" specified more than once David G. Johnston <david.g.johnston@gmail.com>
2015-05-12 15:53 ` Re: ERROR: column "gid" specified more than once ktm@rice.edu <ktm@rice.edu>
@ 2015-05-12 16:09 ` David G. Johnston <david.g.johnston@gmail.com>
2015-05-12 16:19 ` Re: ERROR: column "gid" specified more than once David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2015-05-12 16:09 UTC (permalink / raw)
To: ktm@rice.edu <ktm@rice.edu>; +Cc: Jason Aleski <jason.aleski@gmail.com>; pgsql-sql
On Tue, May 12, 2015 at 8:53 AM, ktm@rice.edu <ktm@rice.edu> wrote:
> On Tue, May 12, 2015 at 08:49:53AM -0700, David G. Johnston wrote:
> > On Tuesday, May 12, 2015, Jason Aleski <jason.aleski@gmail.com> wrote:
> >
> > > You probably need to specify your wildcard on both tables.
> > >
> > > CREATE TABLE "BorujerdDistCent" as
> > > SELECT
> > > "Borujerd".*, "Lorestan".*,
> > > t_distance(st_centroid("Lorestan".geometry),"Borujerd".geometry)/1000
> > > as DistFromCntroid
> > > FROM "Borujerd", "Lorestan"
> > >
> > >
> > My bad on the assumed -bugs list from before...
> >
> > Anyway, how is this suugestion different from simply saying "*" without a
> > relation specification - which the OP did and it didn't work.
> >
> > David J.
>
> Because the column names are differentiated by their prefixes then:
>
> Borujerd.gid, Lorestan.gid
>
> No conflict.
>
>
I suggest you test that theory out.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ERROR: column "gid" specified more than once
2015-05-12 08:31 ERROR: column "gid" specified more than once BahramPSQL <bahramjomezade.gis92@gmail.com>
2015-05-12 15:26 ` Re: ERROR: column "gid" specified more than once Jason Aleski <jason.aleski@gmail.com>
2015-05-12 15:49 ` Re: ERROR: column "gid" specified more than once David G. Johnston <david.g.johnston@gmail.com>
2015-05-12 15:53 ` Re: ERROR: column "gid" specified more than once ktm@rice.edu <ktm@rice.edu>
2015-05-12 16:09 ` Re: ERROR: column "gid" specified more than once David G. Johnston <david.g.johnston@gmail.com>
@ 2015-05-12 16:19 ` David G. Johnston <david.g.johnston@gmail.com>
2015-05-12 17:02 ` Re: ERROR: column "gid" specified more than once ktm@rice.edu <ktm@rice.edu>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2015-05-12 16:19 UTC (permalink / raw)
To: ktm@rice.edu <ktm@rice.edu>; +Cc: Jason Aleski <jason.aleski@gmail.com>; pgsql-sql
On Tue, May 12, 2015 at 9:09 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Tue, May 12, 2015 at 8:53 AM, ktm@rice.edu <ktm@rice.edu> wrote:
>
>> On Tue, May 12, 2015 at 08:49:53AM -0700, David G. Johnston wrote:
>> > On Tuesday, May 12, 2015, Jason Aleski <jason.aleski@gmail.com> wrote:
>> >
>> > > You probably need to specify your wildcard on both tables.
>> > >
>> > > CREATE TABLE "BorujerdDistCent" as
>> > > SELECT
>> > > "Borujerd".*, "Lorestan".*,
>> > > t_distance(st_centroid("Lorestan".geometry),"Borujerd".geometry)/1000
>> > > as DistFromCntroid
>> > > FROM "Borujerd", "Lorestan"
>> > >
>> > >
>> > My bad on the assumed -bugs list from before...
>> >
>> > Anyway, how is this suugestion different from simply saying "*" without
>> a
>> > relation specification - which the OP did and it didn't work.
>> >
>> > David J.
>>
>> Because the column names are differentiated by their prefixes then:
>>
>> Borujerd.gid, Lorestan.gid
>>
>> No conflict.
>>
>>
> I suggest you test that theory out.
>
>
The reason why this advice is wrong is because the error is coming from
the CREATE TABLE AS portion and not the select query.
Within the following:
CREATE TABLE testtable AS
SELECT t1.*, t2.*
FROM ( VALUES (1::int) ) t1 (s)
CROSS JOIN ( VALUES (2::int) ) t2 (s)
executing just the SELECT portion will indeed output a two-column result
with both columns named "s".
However, it is not possible to create a table with two columns having the
same name and so using the exact same query will fail with the duplicate
name error.
The only way to solve the problem is to alias the output columns or choose
not to output one of the columns.
SELECT t1.s AS s_t1, t2.s AS s_t2 FROM [...]
or
SELECT t1.* FROM [...]
As shown above column names in the result do not carry over their source
identifier - just the name itself.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: ERROR: column "gid" specified more than once
2015-05-12 08:31 ERROR: column "gid" specified more than once BahramPSQL <bahramjomezade.gis92@gmail.com>
2015-05-12 15:26 ` Re: ERROR: column "gid" specified more than once Jason Aleski <jason.aleski@gmail.com>
2015-05-12 15:49 ` Re: ERROR: column "gid" specified more than once David G. Johnston <david.g.johnston@gmail.com>
2015-05-12 15:53 ` Re: ERROR: column "gid" specified more than once ktm@rice.edu <ktm@rice.edu>
2015-05-12 16:09 ` Re: ERROR: column "gid" specified more than once David G. Johnston <david.g.johnston@gmail.com>
2015-05-12 16:19 ` Re: ERROR: column "gid" specified more than once David G. Johnston <david.g.johnston@gmail.com>
@ 2015-05-12 17:02 ` ktm@rice.edu <ktm@rice.edu>
0 siblings, 0 replies; 8+ messages in thread
From: ktm@rice.edu @ 2015-05-12 17:02 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Jason Aleski <jason.aleski@gmail.com>; pgsql-sql
On Tue, May 12, 2015 at 09:19:23AM -0700, David G. Johnston wrote:
> On Tue, May 12, 2015 at 9:09 AM, David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
> > On Tue, May 12, 2015 at 8:53 AM, ktm@rice.edu <ktm@rice.edu> wrote:
> >
> >> On Tue, May 12, 2015 at 08:49:53AM -0700, David G. Johnston wrote:
> >> > On Tuesday, May 12, 2015, Jason Aleski <jason.aleski@gmail.com> wrote:
> >> >
> >> > > You probably need to specify your wildcard on both tables.
> >> > >
> >> > > CREATE TABLE "BorujerdDistCent" as
> >> > > SELECT
> >> > > "Borujerd".*, "Lorestan".*,
> >> > > t_distance(st_centroid("Lorestan".geometry),"Borujerd".geometry)/1000
> >> > > as DistFromCntroid
> >> > > FROM "Borujerd", "Lorestan"
> >> > >
> >> > >
> >> > My bad on the assumed -bugs list from before...
> >> >
> >> > Anyway, how is this suugestion different from simply saying "*" without
> >> a
> >> > relation specification - which the OP did and it didn't work.
> >> >
> >> > David J.
> >>
> >> Because the column names are differentiated by their prefixes then:
> >>
> >> Borujerd.gid, Lorestan.gid
> >>
> >> No conflict.
> >>
> >>
> > I suggest you test that theory out.
> >
> >
> The reason why this advice is wrong is because the error is coming from
> the CREATE TABLE AS portion and not the select query.
>
> Within the following:
>
> CREATE TABLE testtable AS
> SELECT t1.*, t2.*
> FROM ( VALUES (1::int) ) t1 (s)
> CROSS JOIN ( VALUES (2::int) ) t2 (s)
>
> executing just the SELECT portion will indeed output a two-column result
> with both columns named "s".
>
> However, it is not possible to create a table with two columns having the
> same name and so using the exact same query will fail with the duplicate
> name error.
>
> The only way to solve the problem is to alias the output columns or choose
> not to output one of the columns.
>
> SELECT t1.s AS s_t1, t2.s AS s_t2 FROM [...]
> or
> SELECT t1.* FROM [...]
>
> As shown above column names in the result do not carry over their source
> identifier - just the name itself.
>
> David J.
Yes. You are correct. Sorry for the noise.
Ken
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2015-05-12 17:02 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-12 08:31 ERROR: column "gid" specified more than once BahramPSQL <bahramjomezade.gis92@gmail.com>
2015-05-12 15:07 ` David G. Johnston <david.g.johnston@gmail.com>
2015-05-12 15:26 ` Jason Aleski <jason.aleski@gmail.com>
2015-05-12 15:49 ` David G. Johnston <david.g.johnston@gmail.com>
2015-05-12 15:53 ` ktm@rice.edu <ktm@rice.edu>
2015-05-12 16:09 ` David G. Johnston <david.g.johnston@gmail.com>
2015-05-12 16:19 ` David G. Johnston <david.g.johnston@gmail.com>
2015-05-12 17:02 ` ktm@rice.edu <ktm@rice.edu>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox