[postgis-users] No Primary Key

[Ravi]

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;



      Looking for local information? Find it on Yahoo! Local http://in.local.yahoo.com/