Opened 8 years ago

Closed 8 years ago

Last modified 8 years ago

#6232 closed defect (fixed)

Invalid PGDump created with ogr2ogr when preserve_fid is used

Reported by: alculquicondor Owned by: warmerdam
Priority: normal Milestone: 1.11.4
Component: default Version: 1.11.3
Severity: normal Keywords:


An invalid dump is generated with this command:

ogr2ogr -f PGDump dump.sql PG:dbname=dbname -sql "select * from mytable" -preserve_fid

Relevant lines on the dump:

CREATE TABLE "public"."sql_statement" ( OGC_FID SERIAL, CONSTRAINT "sql_statement_pk" PRIMARY KEY (OGC_FID) );
SELECT AddGeometryColumn('public','sql_statement','wkb_geometry',4326,'GEOMETRY',2);
CREATE INDEX "sql_statement_wkb_geometry_geom_idx" ON "public"."sql_statement" USING GIST ("wkb_geometry");
ALTER TABLE "public"."sql_statement" ADD COLUMN "title" VARCHAR;
INSERT INTO "public"."sql_statement" ("wkb_geometry" , "OGC_FID" , "title") VALUES ('0101000020E6100000FFFFFF6554F652C01B67896282A42BC0', 0 , 'ffff');

OGC_FID when created but "OGC_FID" when inserting.

The issue is solved in gdal 2.x

Change History (3)

comment:1 by Jukka Rahkonen, 8 years ago

I try to explain for those who do not know SQL so well why it is wrong.

In PostgreSQL names of the identifiers are lower case by default and therefore

CREATE TABLE foo (OGC_FID SERIAL); is actually creating a column in lower case "ogc_fid". Later insert which is using column name as quoted "OGC_FID" tries to find literally an upper case column "OGC_FID" and it will fail.

CREATE TABLE foo ("OGC_FID" SERIAL); is forcing the column name into upper case. On the other hand, it means that unquoted

INSERT INTO foo (OGC_FID) VALUES (2); will fail because PostgreSQL in this case searching for a lower case "ogc_fid".

comment:2 by Even Rouault, 8 years ago

Milestone: 1.11.4
Resolution: fixed
Status: newclosed

trunk r31740, branches/2.0 r31741 "PGDump: fix issue with case of ogc_fid field in case the FID layer creation option is not set by user or by ogr2ogr (related to #6232)"

branches/1.11 r31742 "PGDump: fix issue with case of ogc_fid field (e.g. when using ogr2ogr -preserve_fid) (#6232)"

comment:3 by Even Rouault, 8 years ago

trunk r31744, branches/2.0 r31745 "Update expected result of test_ogr2ogr_57 due to #6232 changes"

Note: See TracTickets for help on using tickets.