Re: [gdal-dev] ogr2ogr, preserve_fid and Postgres sequences
Done, thanks for the support:
https://trac.osgeo.org/gdal/ticket/7032
César
2017-09-07 17:41 GMT+02:00 Even Rouault :
> On jeudi 7 septembre 2017 17:05:36 CEST César Martínez wrote:
>
> > Hi Jukka, see answer inline:
>
> >
>
> > 2017-09-07 16:48 GMT+02:00 jratike80 maanmittauslaitos.fi>:
>
> > > Hi César,
>
> > >
>
> > > 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 ;
>
>
>
> Would you mind opening a ticket in Trac about that
>
> https://trac.osgeo.org/gdal/newticket
>
>
>
> Even
>
>
>
> --
>
> Spatialys - Geospatial professional services
>
> http://www.spatialys.com
>
> ___
> gdal-dev mailing list
> gdal-dev@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>
--
SCOLAB
http://scolab.es
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] ogr2ogr, preserve_fid and Postgres sequences
On jeudi 7 septembre 2017 17:05:36 CEST César Martínez wrote:
> Hi Jukka, see answer inline:
>
> 2017-09-07 16:48 GMT+02:00 jratike80 :
> > Hi César,
> >
> > 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 ;
Would you mind opening a ticket in Trac about that
https://trac.osgeo.org/gdal/newticket
Even
--
Spatialys - Geospatial professional services
http://www.spatialys.com
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] ogr2ogr, preserve_fid and Postgres sequences
Hi Jukka, see answer inline:
2017-09-07 16:48 GMT+02:00 jratike80 :
> Hi César,
>
> 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 ;
César
>
> -Jukka Rahkonen-
>
>
>
> --
> Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
> ___
> gdal-dev mailing list
> gdal-dev@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
--
SCOLAB
http://scolab.es
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] ogr2ogr, preserve_fid and Postgres sequences
Hi César, What are the problems you face when editing such layer? Do you have an alternative suggestion about what to do for the sequence? -Jukka Rahkonen- -- Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html ___ gdal-dev mailing list gdal-dev@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev
[gdal-dev] ogr2ogr, preserve_fid and Postgres sequences
Hi, 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. Is this an intended behaviour or should be considered a bug? Note that I am using GDAL version 1.11.3. Example: # Using -preserve_fid ogr2ogr -preserve_fid -update -f PostgreSQL "PG:host='localhost' dbname='test'" myshp.shp myshp -nln "test1" # SELECT last_value from test1_ogc_fid_seq ; last_value 1 (1 row) # 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) Thanks in advance, César -- SCOLAB http://scolab.es ___ gdal-dev mailing list gdal-dev@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev
