4

I've created a bash script with ogr2ogr as the following:

PARAMS=(
    -f "PostgreSQL"
    PG:"host=localhost port=5432 user=user password=password dbname=my_database"
    -nlt GEOMETRY
    -lco SCHEMA="my_schema"
    -lco overwrite=yes
    -lco GEOMETRY_NAME=geometry
    -nln "layer_name"
    "input.geojson"
)
ogr2ogr "${PARAMS[@]}"

It works perfectly fine for importing a table called layer_name inside my_schema onmy_database, it imports the data and if I run this command again it overwrites the table.

Well, that works because I'm using a schema other than the public, if I try to execute this same command on the public schema as following:

PARAMS=(
    -f "PostgreSQL"
    PG:"host=localhost port=5432 user=user password=password dbname=my_database"
    -nlt GEOMETRY
    -lco overwrite=yes
    -lco GEOMETRY_NAME=geometry
    -nln "layer_name"
    "input.geojson"
)
ogr2ogr "${PARAMS[@]}"

Even though it works for importing the data, if I run ogr2ogr with these parameters again I receive the warning:

Warning 1: Layer creation options ignored since an existing layer is being appended to.

It completely ignores the -lco overwrite=yes parameter and it's different from my first sample, which overwrites the table. This second ogr2ogr command will append the data on the table each time I run this command. Why does that happen only on the public schema? Is there any extra parameter that I need to use with overwrite on the public schema to make it work?

1 Answer 1

4

I could reproduce the issue with latest GDAL GDAL 3.5.0dev, released 2022/99/99.

ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=dbname user=user password=password" -nlt geometry -lco geometry_name=geometry -nln overwritetest test.shp -lco overwrite=yes
Warning 1: Layer creation options ignored since an existing layer is
         being appended to.

A workaround is to use the -overwrite switch of ogr2ogr instead

-overwrite

Delete the output layer and recreate it empty

ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=dbname user=user password=password" -nlt geometry -lco geometry_name=geometry -nln overwritetest test.shp -overwrite

I think that you have found a bug and you have enough details to go to https://github.com/OSGeo/gdal/issues and make a bug report.

EDIT It seems that you still cannot edit your script to use the -overwrite option of ogr2ogr everywhere:

SCHEMA: Set name of schema for new table. Using the same layer name in different schemas is supported, but not in the public schema and others. Note that using the -overwrite option of ogr2ogr and -lco SCHEMA= option at the same time will not work, as the ogr2ogr utility will not understand that the existing layer must be destroyed in the specified schema. Use the -nln option of ogr2ogr instead, or better the active_schema connection string.

2
  • Thanks! I got to the conclusion that with -overwrite it works on the public schema but it doesn't work on other ones. The -lco overwrite is the other way around, it works with schemas that are not public but with the public one it doesn't work. And if by any chance I have a table with the same name in the public and different schemas I'll get an error too.
    – raylight
    Commented Apr 21, 2022 at 5:41
  • Also, in my case I'm using GDAL 3.3.2, released 2021/09/01.
    – raylight
    Commented Apr 21, 2022 at 5:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.