10

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

2
  • i think it would help if you could edit your question and show us the database errors as well
    – Curlew
    Commented Dec 21, 2013 at 9:31
  • Oh, not sorted actually - i.e. it works in QGIS, but my software (Open Layers using WFS-T) isn't setting the default. Any ideas?
    – minisaurus
    Commented Dec 21, 2013 at 12:12

2 Answers 2

12

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.

1

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().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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