PostgreSQL PostGIS: append table to another table [closed]

Question

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;

Answer 1

It suggests the existing data in the large table was inserted using an objectID coming from a different sequence than the default one. The new insert fetch objectID = 1 from the sequence, which already exists.

You can use the tool that you previously used to insert the features (i.e. ArcGIS).

Alternatively, you can bump the default sequence to the current biggest ObjectID

SELECT setval('schema.largetable_objectid_seq', max(objectid)) 
FROM schema.largetable; 

However, it could give you some issues if you try to add more features from ArcGIS, as a different sequence may be used