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.