Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#4033 closed defect (fixed)

PGDump driver issues a CREATE SCHEMA with CREATE_TABLE=OFF

Reported by: lpercich Owned by: Even Rouault
Priority: normal Milestone: 1.8.1
Component: OGR_SF Version: svn-trunk
Severity: normal Keywords: PGDump
Cc:

Description

When invoked with -append -lco CREATE_TABLE=OFF and -lco SCHEMA=<schema>, ogr2ogr with the PGDump driver generates a CREATE SCHEMA instruction at the begining of the resulting script.

If no schema is specified (assuming "public"), the "CREATE SCHEMA" instruction is not generated.

When invoked with -overwrite -lco CREATE_TABLE=OFF and -lco SCHEMA=<schema>, the PGDump driver generates a CREATE SCHEMA instruction followed by a "drop table schema.table", which always fails in PostgreSQL.


My thoughts:

When CREATE_TABLE=OFF (inserting into an existing table), no create schema should be issued.

I'd expect a -lco CREATE_SCHEMA=ON|OFF. Are we sure that a CREATE_TABLE=YES should imply a "CREATE_SCHEMA=YES"? If I need to load many MapInfo? tables in Postgres in the same schema with -lco CREATE_TABLE=ON, multiple CREATE SCHEMA instructions are going to fail and we have to remove them by hand (read "by grep|sed").

Also a -lco DROP_TABLE=ON|OFF would be useful, because the PGDump driver cannot guess if the table we're going to create already exists in the destination schema, and Postgres doesn't have a DROP IF EXISTS statement.

(Tested on 1.9dev compiled on Ubuntu 10.04)

Attachments (1)

test_OGR_PGDump_schema.zip (8.0 KB) - added by lpercich 6 years ago.
Test data and script, and resulting sql files

Download all attachments as: .zip

Change History (4)

Changed 6 years ago by lpercich

Attachment: test_OGR_PGDump_schema.zip added

Test data and script, and resulting sql files

comment:1 Changed 6 years ago by Even Rouault

Owner: changed from warmerdam to Even Rouault

This makes sense. I will look at that.

comment:2 Changed 6 years ago by Even Rouault

Keywords: MapInfo removed
Milestone: 1.8.1
Resolution: fixed
Status: newclosed

r22136 + r22137 /trunk/gdal/ogr/ogrsf_frmts/pgdump/ (drv_pgdump.html ogrpgdumpdatasource.cpp): PGDump: add CREATE_SCHEMA and DROP_TABLE layer creation option (#4033)

r22138 /branches/1.8/gdal/ogr/ogrsf_frmts/pgdump/ (drv_pgdump.html ogrpgdumpdatasource.cpp): PGDump: add CREATE_SCHEMA and DROP_TABLE layer creation option (#4033)

Note: DROP TABLE IF EXISTS has been introduced in PostgreSQL 8.2, so I've added the option to emit it by specifying -lco DROP_TABLE=IF_EXISTS

comment:3 in reply to:  2 Changed 6 years ago by lpercich

Even, thank you very much.

Sig

Note: See TracTickets for help on using tickets.