PostgreSQL primary key sequence is not updated when -preserve_fid is used
|Reported by:||cmartinez||Owned by:||warmerdam|
Description (last modified by )
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 ;