I've got over a hundred data layers in shapefile format that I want to upload into a PostGIS database. They are all in the same projection, but represent different data layers so they do not have the same schema.

What is the most time-effective way to bulk convert all of these files into my PostGIS database (on windows 7 OS)?

UPDATE: Bulk importing in the pgAdmin3 "PostGIS shapefile uploader" (as noted below) is now available by default with PostGIS 2.0.

  • I'm looking for a simlar answer to this. However i want to make it a batch job that runs everynight. In effect i want to make my PostGIS a slave to ArcSDE (for the time being). Being new to PostGIS and SQL, i get what the cmd.exe script would do but somehow its not sticking in my head. What i want to do is get a series of shp files that have been exported from ArcSDE as a batch job, then upload these into my PostGIS, which would overwrite the existing gis/tables in place already.
    – geosmiles
    Commented Aug 1, 2011 at 18:47
  • I'd ask this as a new question, along the lines of "how to batch synchronize data from ArcSDE to PostGIS." There could be some interesting ideas.
    – Mike T
    Commented Aug 1, 2011 at 19:14

5 Answers 5


If you have a Windows computer, you can use good 'ol CMD.EXE with a few esoteric for-loops. Make sure you do this in a "contained" directory with only the shp/sql files that you need to load.

First step, create the SQL loader files (I also assumed you have Lat/Long WGS84 data with 4326 .. update this to your SRS):

for %f in (*shp) do shp2pgsql -s 4326 %f public.%~nf > %~nf.sql

Then check your SQL files to make sure they look good, then do a similar loop:

for %f in (*sql) do psql -h myserver -d mydb -U myuser -f %f > nul

The bash equivalent for POSIX folk (Linux, Mac OS X, etc) is something like:

for f in *.shp
    shp2pgsql -s 4326 %f public.`basename $f .shp` > `basename $f .shp`.sql


for f in *.sql
    psql -h myserver -d mydb -U myuser -f $f > /dev/null

or both parts piped into a single loop, if you don't need to keep the interim .sql files:

for f in *.shp
    shp2pgsql -s 4326 %f public.`basename $f .shp` | psql -d mydb > /dev/null
  • would this work better in a single loop, for f in *.shp do shp2pgsql -s 4326 %f public.'basename $f .shp' > 'basename $f .shp'.sql | psql -h myserver -d mydb -U myuser done
    – Sam007
    Commented Nov 21, 2012 at 23:47
  • yup, it can be done in one command (but without the > redirect part, as this breaks the | pipe part), as long as you are sure the SQL passed to psql is correct. I'd argue that this is not better, since there is no record of the data in SQL format.
    – Mike T
    Commented Nov 22, 2012 at 0:37

If want to stick to a GUI then the newer version of pgAdmin has Shapefile Loader that can be used as a bulk load

enter image description here


You can also use this single command which helps in looping much easier and also does not need to create .sql separately,

for f in *.shp
    do shp2pgsql -c -D -s 4326 -I $f public.${f%.*} | psql -h hostname -d dbname -U usrname

You might also want to look at SPIT, which is a PostGIS loader plugin for QGIS


A bit late to the party, but this was the first result in a google search for this question.

I'm not sure how you've been able to do this, but you can now just drag any shapefiles you have loaded in QGIS (from the Layers panel) to whichever schema you want to load them to (in the Browser panel). You can do one or many.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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