Postgres Pro
Downloads
Home   >   mailing lists

Thread: create table as problem

create table as problem

From
"Frances Collier"
Date:

I am trying to copy some rows from a table to another table using “create table as” but I keep getting a “column name ‘ogc_fid’ is duplicated” error. Can anyone tell me why this is happening?

 

The postgres statement:

 

create table skamaniaTable as

select *

from wacounties a,

wacounties b

where b.county_nm='Skamania' and

a.wkb_geometry && b.wkb_geometry and

distance(a.wkb_geometry, b.wkb_geometry) = 0;

 

The table fields:

 

ogc_fid :: int, wkb_geometry :: geometry, area :: float, perimeter :: float, wacounties :: int, wacounti_1 :: int, county_cod :: int, county_fip :: varchar, county_nm :: varchar, ecy_region :: varchar, air_region :: varchar, shape_leng :: float, shape_area :: float

 

Any help would be greatly appreciated.

 

Thank you,

Frances Collier

 

Re: create table as problem

From
Bruno Wolff III
Date:
On Wed, Sep 28, 2005 at 10:07:34 -0700,
  Frances Collier <fcollier@preparedresponse.com> wrote:
> I am trying to copy some rows from a table to another table using "create
> table as" but I keep getting a "column name 'ogc_fid' is duplicated" error.
> Can anyone tell me why this is happening?

You need to have unique column names. Since wacounties is joined to itself
every column name will appear twice. You need to qualify them.
I think you could use a.*, b.* if you really don't want to write out the
full column names.

>
>
>
> The postgres statement:
>
>
>
> create table skamaniaTable as
>
> select *
>
> from wacounties a,
>
> wacounties b
>
> where b.county_nm='Skamania' and
>
> a.wkb_geometry && b.wkb_geometry and
>
> distance(a.wkb_geometry, b.wkb_geometry) = 0;
>
>
>
> The table fields:
>
>
>
> ogc_fid :: int, wkb_geometry :: geometry, area :: float, perimeter :: float,
> wacounties :: int, wacounti_1 :: int, county_cod :: int, county_fip ::
> varchar, county_nm :: varchar, ecy_region :: varchar, air_region :: varchar,
> shape_leng :: float, shape_area :: float
>
>
>
> Any help would be greatly appreciated.
>
>
>
> Thank you,
>
> Frances Collier
>
>
>

Re: create table as problem

From
Bruno Wolff III
Date:
On Wed, Sep 28, 2005 at 10:07:34 -0700,
  Frances Collier <fcollier@preparedresponse.com> wrote:
> I am trying to copy some rows from a table to another table using "create
> table as" but I keep getting a "column name 'ogc_fid' is duplicated" error.
> Can anyone tell me why this is happening?

I made a slight oops in my last message, the newly created table needs unique
names so a.*, b.* won't work unless you explicitly name the columns for it.