On Mon, Sep 14, 2009 at 5:34 PM, Ravi <[email protected]> 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 > [email protected] > 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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
