ogr2ogr -overwrite option does not work with -lco SCHEMA= option

[hdus]

I try to load an ESRI shape file into a PG schema, different from public with -lco SCHEMA=myschema. The schema "myschema" exists and is empty. When I load the shape to PG everything works fine. But when I want to overwrite these data with the ogr2ogr -overwrite option ogr2ogr stops with the error: "relation xxxx already exists". Running ogr2ogr with CPL_DEBUG=ON the output shows: "CREATE TABLE "myschema"."xxxx" ....". IMO ogr2ogr should delete the the table xxxx before createing a new one.

The -overwrite option works fine without using -lco SCHEMA=myschema.

[Even Rouault]

Yes, this is a very good point and a tricky issue that would tend to show that use of -lco SCHEMA=myschema should be discouraged in that case.

Here's a workaround : ogr2ogr -overwrite -f PostgreSQL PG: yourtable.shp yourtable -nln myschema.yourtable

The explanation is the following : When using -lco SCHEMA, ogr2ogr doesn't understand it and tries to delete the existing layer whose name is then 'yourtable', so it tries to delete the table in the 'public' schema. It is only the CreateLayer() method of the PG driver that would understand -lco SCHEMA (lco, meaning layer CREATION option...) and create 'myschema'.'yourtable', but as it already exists, it fails...

Another possibility if you use trunk version of GDAL/OGR with the new active_schema option is : ogr2ogr -overwrite -f PostgreSQL PG:active_schema=myschema yourtable.shp yourtable

So as I've proposed 2 workarounds, I'll close that ticket as I don't see any way (that wouldn't involve dirty hack) of fixing ogr2ogr to understand that it should delete myschema.yourtable when -lco SCHEMA= is used.

I've also added some precision (r16218) in the PG driver documentation to document that workaround.

[hdus]

Replying to rouault:

Yes, this is a very good point and a tricky issue that would tend to show that use of -lco SCHEMA=myschema should be discouraged in that case.

Here's a workaround : ogr2ogr -overwrite -f PostgreSQL PG: yourtable.shp yourtable -nln myschema.yourtable

The explanation is the following : When using -lco SCHEMA, ogr2ogr doesn't understand it and tries to delete the existing layer whose name is then 'yourtable', so it tries to delete the table in the 'public' schema. It is only the CreateLayer() method of the PG driver that would understand -lco SCHEMA (lco, meaning layer CREATION option...) and create 'myschema'.'yourtable', but as it already exists, it fails...

Another possibility if you use trunk version of GDAL/OGR with the new active_schema option is : ogr2ogr -overwrite -f PostgreSQL PG:active_schema=myschema yourtable.shp yourtable

So as I've proposed 2 workarounds, I'll close that ticket as I don't see any way (that wouldn't involve dirty hack) of fixing ogr2ogr to understand that it should delete myschema.yourtable when -lco SCHEMA= is used.

I've also added some precision (r16218) in the PG driver documentation to document that workaround.

Thank you for your quick response. Your first workaround maybe work fine with single table sources. But when you want to import p.e. INTERLIS this source can contain hundreds of tables to import to PostGIS. Thats the reason why -lco SCHEMA was introduced. The -nln option will not work with this workaround due to the fact that I don't have one single table to import.

[Even Rouault]

Your point about being able to overwrite multiples tables in a schema at once was one of the reason why I've introduced the active_schema=/schemas= connection string in the PG driver. I've tried to improve again my explanations by promoting the use of active_schema in your scenario in r16220.

I'll close the ticket again as :

• I've given a functionnal workaround for all reasonnably recent GDAL versions. Combined with some scripting (iterating over the layers and using -nln) or other tricks (like destroying the schema and recreating again), that should solve your use case.

Here's a simple shell script that translates the shapefiles contained in the shapes_apt200810 directory (would work similarly with an INTERLIS datasource) in the apt200810 PostgreSQL schema :

#!/bin/sh
LAYERS=$(ogrinfo -ro -so shapes_apt200810 | grep ": " | grep "(" | awk '{print $2}')
for i in $LAYERS; do
  echo "Converting $i...";
   ogr2ogr -overwrite -f PostgreSQL PG: shapes_apt200810 $i -nln apt200810.$i;
done

• the new active_schema connection string is the right fix in the longer term.