PostgreSQL primary key sequence is not updated when -preserve_fid is used

[cmartinez]

When loading a layer on PostGIS using ogr2ogr, a sequence is created for the serial primary key. If -preserve_fid paramenter is provided, the sequence start is not properly initialized (i. e. sequence starts on 1). This leads to problems if the layer is later edited.

Note that I am using GDAL version 1.11.3.

For instance, using --preserve_fid

ogr2ogr -preserve_fid -update -f PostgreSQL "PG:host='localhost' dbname='test'" myshp.shp  myshp -nln "test1"

We get the following sequence status:

# SELECT last_value from test1_ogc_fid_seq ;
 last_value
------------
          1
(1 row)

Instead, if we don't use -preserve_fid, the sequence is properly initialized to 102:

# Not using -preserve_fid: sequence properly initialized to 102
ogr2ogr -update -f PostgreSQL "PG:host='localhost' dbname='test'" myshp.shp  myshp  -nln "test2"

# SELECT last_value from test2_ogc_fid_seq ;
 last_value
------------
        102
(1 row)

What are the problems you face when editing such layer?

If you later try to add a new record to the layer without providing an id, it will raise an error, defeating the purpose of having a serial PK. For instance:

INSERT INTO test1 (wkb_geometry)
VALUES (ST_GeomFromText('MULTIPOINT(-5.34510248270791 36.1506091987899)', 4258)) ;

ERROR: duplicate key value violates unique constraint "test1_pkey"
SQL state: 23505
Detail: Key (ogc_fid)=(1) already exists.

They you must provide the ogc_fid by hand:

INSERT INTO test1 (ogc_fid, wkb_geometry)
VALUES (102, ST_GeomFromText('MULTIPOINT(-5.34510248270791 36.1506091987899)', 4258)) ;

Do you have an alternative suggestion about what to do for the sequence?

The sequence should be initialized in the same way as it is done when -preserve_fid is not provided. For the layer & sequence in the example, the following SQL statement would work:

SELECT set_val('test1_ogc_fid_seq', max(ogc_fid)) FROM test1 ;