I have a large polygon table (30+million rows) and a smaller polygon table (45,000 rows). Both are PostGIS enabled. The smaller table only contains polygons that do not exist in the large table. Both tables have columns named poly_id, ref_no, status and a geometry column (geom). The large table has a few extra columns that aren't important.
My query in pgAdmin is:
INSERT INTO schema.largetable(geom, poly_id, ref_no, status) SELECT geom, poly_id, ref_no, status FROM schema.smalltable;
However I get the error:
ERROR: duplicate key value violates unique constraint "largetable_pkey" DETAIL: Key (objectid)=(1) already exists SQL state: 23505
the small table doesn't have an 'objectid' column and even if both table has duplicate id values, I don't need the id to carry across from the small table. Can't I just append the small table to the large with new id numbers created for the new additional rows?
Here are the columns in large table: objectid (pk, integer), poly_id (integer), ref_no(charvar 9), insert_ (charvar 20), update_ (charvar 20), status (charvar 1), shape_length (double), shape_area (double), wkb_geometry (geometry)
small table: poly_id (pk, integer), ref_no(charvar 9), insert_ (charvar 20), update_ (charvar 20), status (charvar 1), wkb_geometry (geometry)
My 'create table' script for the large table is:
CREATE TABLE schema.largetable ( objectid integer NOT NULL DEFAULT nextval('schema.largetable_objectid_seq'::regclass), poly_id integer, ref_no character varying(9) COLLATE pg_catalog."default", insert_ character varying(20) COLLATE pg_catalog."default", update_ character varying(20) COLLATE pg_catalog."default", status character varying(1) COLLATE pg_catalog."default", shape_length double precision, shape_area double precision, wkb_geometry geometry(MultiPolygon,27700), CONSTRAINT largetable_pkey PRIMARY KEY (objectid) ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; ALTER TABLE schema.largetable OWNER to user_admin;