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?

Improve this question
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. Commented Mar 9, 2024 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 Commented Mar 11, 2024 at 5:35

1 Answer 1

Reset to default
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}"
Improve this answer
2
  • 2
    GDAL can create a new schema, but not ogr2ogr. Try ogrinfo -sql "CREATE SCHEMA IF NOT EXISTS my_schema" PG.... Commented Mar 9, 2024 at 20:42
  • Fixed. Thanks for the precision! Commented Mar 9, 2024 at 21:15

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.