PostGIS – Solving ogr2ogr Failure When Importing to PostgreSQL 12

ogr2ogrpostgispostgresql

Using CentOS 8.0 with PostgreSQL 12, PostGIS 3, and GDAL/OGR 3.0.1.
I want to append a file to a database table.

Previously (before upgrading to the new system) I used 

ogr2ogr -f "PostgreSQL" PG:"dbname=dev user=admin password=123 host=0.0.0.0 port=5432" -lco "SCHEMA=myschema" -lco "GEOMETRY_NAME=geog" -append -sql "select ID as gid from input" input.gen

However, that fails now with

ERROR 1: ERROR: column s.consrc does not exist
LINE 1: …nrelid = c.oid AND a.attnum = ANY (s.conkey) AND (s.consrc L…
^

HINT: Perhaps you meant to reference the column "s.conkey" or the column "s.c onbin".

ERROR 1: ERROR: column s.consrc does not exist
LINE 1: …nrelid = c.oid AND a.attnum = ANY (s.conkey) AND (s.consrc L…
^

HINT: Perhaps you meant to reference the column "s.conkey" or the column "s.c onbin".

ERROR 1: Layer myschema.input already exists, CreateLayer failed.
Use the layer creation option OVERWRITE=YES to replace it.

ERROR 1: Terminating translation prematurely after failed
translation from sql statement.

If I make a PGDUMP with

ogr2ogr -f "PGDUMP" -lco "SCHEMA=myschema" -lco "GEOMETRY_NAME=geog" -append -sql "select ID as gid from input" test.dump input.gen

I can run that file in psql and import the data, but I would like to avoid that extra step of writing a large pgdump file.

Notes

  • It also fails without -append
  • It fails with other input data types
    and files as well (Shapefile, ArcGEN, and GeoJSON)
  • Columns mentioned in the Error message are not part of my input data.
  • I suspect that it has something to do with PostgreSQL 12 dropping OID

Best Answer

I have the same issue (this morning) and started to search for some solutions and the only thing I found is that is a GDAL PG12 problem, which was solved in a commit of GDAL in July 2019.

This means we have to fix this manually or upgrade our GDAL. Your GDAL version is from June 2019, mine also.

Related Question