0

I'm trying to import a dataset (file GDB) into a PostgreSQL (16) + PostGIS (3.4) database using GDAL 3.6.3 (released 2023/03/07) without any kind of success:

ogr2ogr -f PostgreSQL "PG:${PG_URI}" -doo ACTIVE_SCHEMA=my_schema "${file_gdb}"
Warning 1: -doo ignored when creating the output datasource.

also:

ogr2ogr -f PostgreSQL "PG:${PG_URI}" -lco SCHEMA=my_schema "${file_gdb}"
Warning 1: Layer creation options ignored since an existing layer is
         being appended to.

or:

ogr2ogr -f PostgreSQL "PG:${PG_URI}" -dsco SCHEMA=my_schema "${file_gdb}"
Warning 6: driver PostgreSQL does not support creation option SCHEMA

I also tried to expand the connection parameters one by one (but I truly want to use a URI connection string):

ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=mydb user=myuser password=mypassword ACTIVE_SCHEMA=my_schema" "${file_gdb}"

This takes time and doesn't raise anything special but it writes tables to the public schema.


From here, two tightly related questions:

  • do I have to manually create the PostgreSQL schema before using ogr2ogr?
  • how to specify the target schema (in both cases: it already exists/it doesn't already exist) when importing a dataset such as a file GDB (it can be a GPKG)? By default, the public schema is used.
Doc:

https://gdal.org/drivers/vector/pg.html#dataset-open-options
https://gdal.org/programs/ogr2ogr.html

Related:

Uploading file geodatabase to PostgreSQL database schema?

2
  • 2
    -dsco cannot work because the driver does not support any datastore creation options gdal.org/drivers/vector/pg.html. And -doo is a dataset open option and does not affect writing. It is not documented that GDAL cannot create a new schema, but if you try, you'll see a message like ERROR 1: Schema "theschema" does not exist. Your second command should work but the warning suggests that you have already a table with the same name.
    – user30184
    Commented Mar 9 at 17:23
  • 1
    I guess it would work to define the schema in the -nln parameter. For example: ogr2ogr .... -nln my_schema.my_layername ... my_source
    – andré
    Commented Mar 11 at 5:35

1 Answer 1

1

Okay, so according to both BERA and user30184 comments, one must:

  1. Be sure a table with the same name doesn't exist already in the database public schema (it was my case because of a prior run of one of the commands I tried).

  2. Create the schema manually within PostgreSQL itself because the ogr2ogr tool cannot do that:

CREATE SCHEMA IF NOT EXISTS my_schema
    AUTHORIZATION postgres;

If the schema doesn't already exist, the following error is raised:

ERROR 1: ERROR:  schema "my_schema" does not exist
LINE 1: CREATE TABLE "my_schema"."building_layer" 
  1. Run the GDAL command with the -lco option:
ogr2ogr -f PostgreSQL "PG:${PG_URI}" -lco SCHEMA=my_schema "${file_gdb}"
2
  • 2
    GDAL can create a new schema, but not ogr2ogr. Try ogrinfo -sql "CREATE SCHEMA IF NOT EXISTS my_schema" PG....
    – user30184
    Commented Mar 9 at 20:42
  • Fixed. Thanks for the precision! Commented Mar 9 at 21:15

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.