2

Context

I have a PostGIS database.

If I use: 

shp2pgsql -I -s 4326 -d "my_shapefile.shp" myschema.vectordata \
| psql -h localhost -U username -d my_database -v ON_ERROR_STOP=1

To inject data from a Shapefile source, I got the error: 

Shapefile type: Arc
Postgis type: MULTILINESTRING[2]
SET
SET
ERROR:  column not found in geometry_columns table
CONTEXT:  PL/pgSQL function dropgeometrycolumn(character varying,character varying,character varying,character varying) line 34 at RAISE
SQL statement "SELECT myschema.DropGeometryColumn('',$1,$2,$3)"
PL/pgSQL function dropgeometrycolumn(character varying,character varying,character varying) line 5 at SQL statement

If I remove the -d flag, it goes well.
So I guess that, as stated in the documentation; 

-d     Drops the database table before creating a new table with the data in the Shape file.

it will drop the database table without checking if it exists before. So in the case tables exist, it goes well, if not, it fails as stated before.

Question

How could I make it equivalent to somehow a drop "IF EXISTS" sql statement when dropping the database table prior to injecting new data from a Shapefile?

Informations;

"PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit"

"2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"

JGH
  • 41,794
  • 3
  • 43
  • 89
swiss_knight
  • 10,309
  • 9
  • 45
  • 117
  • 2
    A workaround could be to run the command with -p switch first. Then you should have always something to drop with the next step. Or just send DROP TABLE with psql first. Or use ogr2ogr https://gdal.org/programs/ogr2ogr.html with -overwrite. – user30184 Mar 13 '20 at 15:24
  • Adding the -p flag prior to the -d doesn't change the situation. – swiss_knight Mar 13 '20 at 15:35
  • 1
    I did not mean that but running the whole command with -p first. – user30184 Mar 13 '20 at 15:36
  • 2
    I asked a similar question a few years ago. The solution was to use ogr2ogr instead. The other approach, if you want to stick with shp2pgsql, is to have a batch script which checks whether the table exists or not and then run the shp2pgsql with the appropriate switch. – John Powell Mar 16 '20 at 12:32
  • I've read here an interesting comment on using ogr2ogr or shp2pgsql: "The pgsql2shp and shp2pgsql are usually the best tools for converting back and forth between PostGIS and ESRI for 2 main reasons.". I'm still having some same issue while using ogr2ogr; it cannot find the postgis installation because it was set in an other schema. I don't know how to make the tool aware of that? – swiss_knight Apr 02 '20 at 16:31
  • Actually, this: https://gis.stackexchange.com/questions/357006/making-postgis-extension-persist-in-the-search-path-of-a-postgresql-database solves the latter. Now the tools are able to find PostGIS functions. – swiss_knight Apr 05 '20 at 17:45

0 Answers0