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)
TABLESPACE pg_default;

ALTER TABLE schema.largetable
    OWNER to user_admin;
  • 1
    You haven't actually attempted to change the id values, at least, not according to the SQL you've provided. Please Edit the question to provide more information about the actual table definitions, the 'objectid' constraint that is failing, and how the objectid value is being populated in the second table.
    – Vince
    Mar 4, 2019 at 16:22
  • 1
    I'm saying there's something exceedingly strange about an INSERT that results in a duplicate key without populating any values. We need the CREATE TABLE of both tables, and any DEFAULT population info and constraints. This is at the cusp of a Database Administrators question, because it's so deep into PostgreSQL functionality.
    – Vince
    Mar 4, 2019 at 16:33
  • 1
    I don't want you to create a new table. I want you to tell us what PostgreSQL reports is the CREATE TABLE statement. You can't append until you identify the conflict, which is wrapped in how the table is defined.
    – Vince
    Mar 4, 2019 at 16:38
  • 1
    And there it is. You have a NEXTVAL which fires a sequence. Since this table is part of a registered geodatabase, you would probably have an easier time using ArcGIS tools to manage the append. Doing this from SQL will likely exceed your novice status (and would challenge mine, with 25 years of ArcSDE hacking behind me)
    – Vince
    Mar 4, 2019 at 16:51
  • 2
    What is the value of nextval in your sequence? It should be something bigger than the largest objectid that you have in your large table, otherwise the sequence is feeding duplicates.
    – user30184
    Mar 4, 2019 at 18:07

1 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

  • I'll give this a try but to clarify, I'm not using ArcGIS to upload to Postgres. I'm using ogr2ogr in shell command or the standalone shapefile uploader that comes with he postgres install- PostGIS Shapefile and DBF loader exporter 2.0.
    – Theo F
    Mar 5, 2019 at 16:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.