#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: | |
Cc: |
Description
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 , 8 years ago
comment:2 by , 8 years ago
Milestone: | → 1.11.4 |
---|---|
Resolution: | → fixed |
Status: | new → closed |
comment:3 by , 8 years ago
Note:
See TracTickets
for help on using tickets.
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".