[GIS] Ogr2ogr – shape to postgis – is there a way to use and preserve an existing primary key

ogrpostgis-2.0postgresqlshapefile

I've been struggling with this for a while, tried a few different ideas inspired by these pages:

But I always end up with ogc_fid as the primary key. Anyone know of a way to get ogr2ogr to use an existing primary key for the fid?

Alternatively, does anyone know how I can get this existing key to be an autoincrement sequence after the ogr2ogr load?

I've tried:

CREATE SEQUENCE my_table_my_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;    

ALTER TABLE public.my_table_my_pk_seq OWNER TO postgres;    
ALTER SEQUENCE my_table_my_pk_seq OWNED BY my_table.my_pk;    
ALTER TABLE ONLY my_table ALTER COLUMN my_pk SET DEFAULT nextval('my_table_my_pk_seq'::regclass);    
ALTER TABLE my_table ALTER COLUMN my_pk SET NOT NULL;

SELECT pg_catalog.setval('my_table_ogc_fid_seq', NUMBER_OF_FEATURES_IMPORTED, true);`

But I get database errors when trying to insert features.

So, to get it to work with wfs-t, I deleted ogc_fid and made my_pk the primary key

Best Answer

You can add the -lco FID=[your_id_column] switch to your OGR2OGR script.

Eg.

ogr2ogr -f "PostgreSQL" PG:"host=myhost user=myloginname dbname=mydbname 
password=mypassword" mytabfile.shp -lco FID=id

Where your primary key column is called id.

Related Question