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;

closed as off-topic by Vince, whyzar, Andre Silva, Jochen Schwarze, neogeomat Mar 11 at 10:19

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions relating to general IT or with no clear GIS component, are off-topic here but can be researched/asked at Stack Overflow (software development), Super User (computing hardware and software), Database Administrators (relational databases) and other SE sites" – Vince, whyzar, Andre Silva, Jochen Schwarze, neogeomat
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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 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 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 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 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 at 18:07

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 at 16:04

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