Opened 13 years ago

Closed 13 years ago

#2821 closed defect (fixed)

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

Reported by: hdus Owned by: warmerdam
Priority: normal Milestone:
Component: default Version: unspecified
Severity: major Keywords:
Cc:

Description

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.

Change History (3)

comment:1 Changed 13 years ago by Even Rouault

Resolution: fixed
Status: newclosed
Summary: ogr2ogr -overwrite option does not work with schema qualified PG layersogr2ogr -overwrite option does not work with -lco SCHEMA= option

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.

comment:2 in reply to:  1 Changed 13 years ago by hdus

Resolution: fixed
Status: closedreopened

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.

comment:3 Changed 13 years ago by Even Rouault

Resolution: fixed
Status: reopenedclosed

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.
Note: See TracTickets for help on using tickets.