PG driver doesn't set FID in COPY mode if FID column isn't a regular column

[Even Rouault]

... which, IMHO, cannot happen (if pszFIDColumn != NULL, then it is excluded from the set of regular columns). Furthermore, there was an inconsistence between the BuildCopyFields() method that set the "header" of the COPY command and the CreateFeatureViaCopy() method : the order of the FID and geometry columns were inverted ! That could definitely not work.

Use case for that ticket : ​http://lists.osgeo.org/pipermail/gdal-dev/2012-February/031720.html

Hi

When using the config PG_USE_COPY option the copy command does not include the destination FID in the field list. In my case the destination FID field is a primary key with a not null constraint, so the copy fails.

e.g.

ogr2ogr --config PG_USE_COPY TRUE -append -preserve_fid -f "PostgreSQL" "PG:host=localhost user=myuser dbname=my_db" test.vrt -lco GEOMETRY_NAME=shape -nln test3 -select id,data1,data2

when the destination table looks like this:

CREATE TABLE test3
(
  id integer NOT NULL PRIMARY KEY,
  data1 TEXT,
  data2 TEXT,
  shape geometry
);

However when I don't use the PG_USE_COPY option and the driver uses plain SQL inserts it works e.g:

ogr2ogr -append -preserve_fid -f "PostgreSQL" "PG:host=localhost user=myuser dbname=my_db" test.vrt -lco GEOMETRY_NAME=shape -nln test3 -select id,data1,data2

Looking down into this it seems it has to do with the layers FieldDefn not including the FID in its list, and the OGRPGTableLayer::BuildCopyFields() method only adding the FID to the copy field list if it's in the layer FieldDefn. While the CreateFeatureViaInsert() method only checks if the FID field is set for the layer and the feature itself has the FID data set.

Is there a specific reason for the OGRPGTableLayer::BuildCopyFields (and CreateFeatureViaCopy) checking that the field must exist in the layer FieldDefn before adding it to the copy field list? Or is this a bug?

Cheers,
Jeremy

[Even Rouault]

r23922 /trunk/ (3 files in 2 dirs): PG: fix insertion of features with FID set in COPY mode (#4495)

[Even Rouault]

r25365 "PGDUMP: fix insertion of features with FID set in COPY mode (#4495)"