Re: ERROR: column "gid" specified more than once

On Tue, May 12, 2015 at 9:09 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Tue, May 12, 2015 at 8:53 AM, ktm(at)rice(dot)edu <ktm(at)rice(dot)edu> wrote:
>
>> On Tue, May 12, 2015 at 08:49:53AM -0700, David G. Johnston wrote:
>> > On Tuesday, May 12, 2015, Jason Aleski <jason(dot)aleski(at)gmail(dot)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.