Create blank table from unix shell

Question

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?

Answer 1

The psql option for executing sql commands is -c and not -e. The -e option means (from the psql help):

-e, --echo-queries echo commands sent to server

Because the -e option does not take arguments, your sql command is simply ignored.

What you need is:

-c, --command=COMMAND run only single command (SQL or internal) and exit

Also,this should work:

psql -h localhost -d mb_sed_class -U postgres -c 'CREATE TABLE "Segment_029_2009_x_y_sedclass_dt_topo_25cm" ...........'

For more infos:

psql --help

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:

cd C:/Program\ Files/PostgreSQL/9.4/bin;