GDAL/OGR import file GDB to a specific PostgreSQL schema

Question

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?

• user30184: -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.
• andré: I guess it would work to define the schema in the -nln parameter. For example: ogr2ogr .... -nln my_schema.my_layername ... my_source

Answer 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" 

3. Run the GDAL command with the -lco option:

ogr2ogr -f PostgreSQL "PG:${PG_URI}" -lco SCHEMA=my_schema "${file_gdb}"

• user30184: GDAL can create a new schema, but not ogr2ogr. Try ogrinfo -sql "CREATE SCHEMA IF NOT EXISTS my_schema" PG....
• swiss_knight: Fixed. Thanks for the precision!