Using an existing field as primary key with ogr2ogr?

Question

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

• http://www.gdal.org/ogr2ogr.html
• http://www.gdal.org/ogr/drv_pg.html
• http://www.gdal.org/ogr/drv_pg_advanced.html
• http://trac.osgeo.org/gdal/wiki/ConfigOptions

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

Answer 1

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.

Answer 2

I've managed to get the field acting as an autoincrement - by reading this https://stackoverflow.com/questions/9490014/adding-serial-to-existing-column-in-postgres and then simplyfying the sql:

CREATE SEQUENCE my_table_my_pk_seq;
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');
ALTER TABLE my_table ALTER COLUMN my_pk SET NOT NULL;
SELECT setval('my_table_my_pk_seq', 9089);

I'm afraid I've lost the database errors, but on the first insert, PostgreSQL seemed to be looking for 9090 in the sequence and didn't like 9089...

I suspect the 'true' in my original setval() call, or maybe the 'regclass' in my original nextval().