According to the shp2pgsql help, the options drop, append, create and prepare are mutually exclusive. So, if I want to create a table from a shape and then append multiple other shapefiles, I do something like the following, keeping a counter to indicate whether we are in create or append mode.

for shp in $(ls *.shp); do

if [ $cnt -eq  0 ]  ; then

   shp2pgsql -s 27700 -c $shp schema.table_name | psql -h localhost db 


   shp2pgsql -s 27700 -a $shp schema.table_name | psql -h localhost db 


This works, as expected, but I have often wondered if there is a simpler way?


If you are able to use ogr2ogr it will ignore create options when appending, and ignore append options when creating.

for shp in $(ls *.shp);
  ogr2ogr -f "PostgreSQL" PG:dbname=databasename -append -a_srs 27700 -nln schema.table_name $shp

Or in windows in the command line:

for /R %f in (*.shp) do ogr2ogr -f "PostgreSQL" PG:dbname=databasename -append -nln schema.table_name "%f"
| improve this answer | |
  • 1
    Sure, ogr2ogr works. Figured there would be a simple solution. Thanks – John Powell Dec 13 '17 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.