[gdal-dev] Inserting into PostGIS table with primary key using ogr2ogr

[Even Rouault]

Selon Luca Sigfrido Percich <sigfrido at tiscali.it>:

>
> Even,
>
> Il giorno mar, 13/12/2011 alle 13.18 +0100, Even Rouault ha scritto:
> > Selon Luca Sigfrido Percich <sigfrido at tiscali.it>:
>
> > Yes, when the driver detects a PK column, it considers it as the FID
> column,
> > which is a particular column, different from the regular columns. So it
> will be
> > ommited in the INSERT statements, unless the feature to be inserted as a
> FID
> > value set. Which is not the case by default when using ogr2ogr.
> >
> > I'd suggest you to try to add the -preserve_fid option to ogr2ogr.
>
> thank you, I missed the -preserve_fid option which is listed at the
> beginning of the documentation page in http://www.gdal.org/ogr2ogr.html,
> but is not further documented, so please add the description to the
> docpage.
>
> With -preserve_fid the shapefile is being loaded with no errors; but the
> values 0,1,2...n are inserted in the existing integer PK column: 0 in
> the first row, 1 in the second and so on. So it seems that this option
> ignores the PK values from the shapefile, too.

It will use as FID the value reported by ogrinfo on the shapefile :

OGRFeature(xxx):FID
  FIELD1 (type) = value

This is in fact the record number in the shapefile, and not a value of a
particular column in the DBF.

>
> Following your explanation, I tried, to no avail, to tell OGR that my
> column id is the FID, with
> -sql "select id as FID, name, ogr_geometry from test_pk_data"
> (tried also ogr_fid and ogc_fid aliases)

No, it is useless to try something like that. You cannot change which column is
considered as the FID column.

>
> > Looking at the code (in trunk), it isn't obvious that the PG and SQLite
> driver
> > would behave differently, but I haven't actually tried, and if you are not
> using
> > trunk, it is possible that there have been changes that might have altered
> the
> > behaviour w.r.t older GDAL/OGR versions.
>
> Actually I'm compiling from trunk. I tested with a SQLite table with PK,
> and the shapefile gets loaded with no complains.

I guess that SQLite must use the _rowid_ as its PK, and so your column is
considered as a regular column.

>
> I will send you a gz file with test shapefile and PG/SQLite SQL scripts.

That will help understand better certainly. Make sure to provide the exact SQL
definition of the tables where you want to insert the data and the command lines
used.

Not sure what can be done to "fix" the behaviour you observe. For the moment,
I've no better suggestion than the workaround you found by dropping temporarily
the PK constraint. Perhaps a configuration option the PG driver could be created
to tell "expose the column you consider to be the PK as being the OGR FID column
as a regular column". Or perhaps it could be autodetected if the PK column is
detected as not being automatically incremented.

>
> I'm waiting your advice to file the bug.
>
> Thanks again
>
> Sig
>
>