I am in the middle of trying to create my first PostGiS database and seem to be running into problems developing scripts to automate importing the various spatial formats so I can use them in my analysis. I am writing today trying to develop a way to batch import shape files into separate tables. From my research, it looks like i need to have empty tables created before i can batch shp2pgsql
command from postgis. In an effort to make this question tractable and leave some learning for myself, I am trying to create empty tables in my database using the structure from a table named test
in my PostgreSQL database from a unix shell. Here is script I have been developing:
#!/bin/sh
for FILE in c:/workspace/CM/mb_sed_class/output2/*.shp
do
echo $FILE
filename="${FILE##*/}"
nakedname="${filename%.shp}"
(cd C:/Program\ Files/PostgreSQL/9.4/bin; psql -h localhost -d mb_sed_class -U postgres -v name='&nakedname' -c "CREATE TABLE name AS SELECT * FROM test WHERE 1=2;")
done
This script creates a table named 'name' and isn't passing the variable into the sql statement. Any ideas on how to pass a variable from the shell to a sql statement?
Best Answer
The psql option for executing sql commands is
-c
and not-e
. The -e option means (from the psql help):Because the -e option does not take arguments, your sql command is simply ignored.
What you need is:
Also,this should work:
For more infos:
PS: I'm not working on windows, but I don't think you need to navigate to the bin directory to execute psql. Try removing this from code: