Обсуждение: ERROR: column "gid" specified more than once

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.

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

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.
>
>

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"

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

<div dir="ltr"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><span
style="font-family:arial,sans-serif">OnTue, May 12, 2015 at 8:53 AM, <a href="mailto:ktm@rice.edu">ktm@rice.edu</a>
</span><spandir="ltr" style="font-family:arial,sans-serif"><<a href="mailto:ktm@rice.edu"
target="_blank">ktm@rice.edu</a>></span><spanstyle="font-family:arial,sans-serif"> wrote:</span><br /></div><div
class="gmail_extra"><divclass="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px
#cccsolid;padding-left:1ex"><div class="HOEnZb"><div class="h5">On Tue, May 12, 2015 at 08:49:53AM -0700, David G.
Johnstonwrote:<br /> > On Tuesday, May 12, 2015, Jason Aleski <<a
href="mailto:jason.aleski@gmail.com">jason.aleski@gmail.com</a>>wrote:<br /> ><br /> > > You probably need
tospecify your wildcard on both tables.<br /> > ><br /> > > CREATE TABLE "BorujerdDistCent" as<br /> >
>SELECT<br /> > >   "Borujerd".*, "Lorestan".*,<br /> > >
t_distance(st_centroid("Lorestan".geometry),"Borujerd".geometry)/1000<br/> > > as DistFromCntroid<br /> > >
FROM"Borujerd", "Lorestan"<br /> > ><br /> > ><br /> > My bad on the assumed -bugs list from
before...<br/> ><br /> > Anyway, how is this suugestion different from simply saying "*" without a<br /> >
relationspecification - which the OP did and it didn't work.<br /> ><br /> > David J.<br /><br
/></div></div>Becausethe column names are differentiated by their prefixes then:<br /><br /> Borujerd.gid,
Lorestan.gid<br/><br /> No conflict.<br /><br /></blockquote></div><br /></div><div class="gmail_extra"><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif">I suggest you test that theory out.</div><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">DavidJ.</div><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">​</div><br/></div></div> 

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