0

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?

  • Multi-line input like this probably doesn't work on Windows with cmd.exe, especially in a subshell. On unix you'd use a here document. I don't know what the cmd.exe alternative is, maybe a tempfile. – Craig Ringer May 20 '15 at 3:15
  • In the process of clarifying my question, I found the solution to my question. To pass a variable from the shell to a sql statement I did not need to use the -v name= flag, I just needed to change "CREATE TABLE name...." to "CREATE TABLE "$nakedname"...". – dubbbdan May 20 '15 at 18:53
0

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;
  • Thanks for the reply! I was off my game yesterday and did not ask a very tractable question. I agree with changing the -e flag to a -c flag to run a sql statement. Also I am I found if i add c:\program files\postgresql\9.4\bin\psql.exe to my system environment path I done need to temporarily change directories to use the psql command. – dubbbdan May 20 '15 at 19:07
  • BTW, shp2pgsql should not need empty tables created in advance. It just outputs the full SQL commands for creating and populating spatial tables, to be piped into pgsql. – Micha May 21 '15 at 8:12

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.