[postgis-users] No Primary Key

[P Kishor]

On Mon, Sep 14, 2009 at 5:34 PM, Ravi <ravivundavalli at yahoo.com> wrote:
> Hi,
> have a problem with a query resulting in a table without a primary key.
> Pl suggest a proper SQL
> Thanks in anticipation
> Ravi
>
> create table coal AS SELECT * from geology where group_='GONDWANA';
>
> From this SQL a table coal is created from table geology with all its columns
> as per the argument group_='GONDWANA'
>
> But the resultant table coal is not having a 'primary key'.
> table geology has gid as primary key.
>
> Pl give me a way how I can get a primary key on table coal.
>
>
> Table geology has (copy pasted from pgadmin)
> CREATE TABLE public.geology
> (
>  gid integer NOT NULL DEFAULT nextval('geology_gid_seq'::regclass),
>  objectid bigint,
>  group_ character varying(35),
>  geometry geometry,
>  CONSTRAINT geology_pkey PRIMARY KEY (gid),
>  CONSTRAINT enforce_dims_geometry CHECK (ndims(geometry) = 2),
>  CONSTRAINT enforce_geotype_geometry CHECK (geometrytype(geometry) = 'MULTIPOLYGON'::text OR geometry IS NULL),
>  CONSTRAINT enforce_srid_geometry CHECK (srid(geometry) = (-1))
> )
> WITH (OIDS=FALSE);
> ALTER TABLE public.geology OWNER TO postgres;
>
> The query results table coal without primary key (copy pasted from pgadmin)
>
> CREATE TABLE public.coal
> (
>  gid integer,
>  objectid bigint,
>  group_ character varying(35),
>  geometry geometry
> )
> WITH (OIDS=FALSE);
> ALTER TABLE public.coal OWNER TO postgres;
>
>
>


Create table "coal" with the correct primary key definition (make the
table defintion the same as the "geology" table) and then

INSERT INTO coal SELECT FROM geology WHERE group_='GONDWANA';



Step 1:

>      Looking for local information? Find it on Yahoo! Local http://in.local.yahoo.com/
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States