How do I use SQL commands inside SH script

Question

My current script looks something like this:

!/bin/sh 
sudo -u postgres createdb mydb
sudo -u postgres psql mydb
CREATE TABLE person (first VARCHAR(32), last VARCHAR(32));
CREATE TABLE thing (size VARCHAR(32), price VARCHAR(32));
exit 0;

As of right now, when I execute the script it creates the database and logs me into it. However, after it logs me into mydb the script does not continue to do the CREATE TABLE commands.

I'm new when it comes to this stuff so I have no idea if I'm going about this the right way. Basically I just want a script that creates a database and then a few tables inside it (using PostgreSQL).

Once you log into postgres your script waits because you're in another application. Take a look at the question and answer here stackoverflow.com/questions/8594717/… it is related to your question. — Joshua
– Joshua, Commented Oct 16, 2012 at 5:41

paxdiablo · Accepted Answer · 2012-10-16 05:29:18Z

3

One method is to use a here-doc (this is a shell feature and depends on what shell you're running):

psql mydb <<EOF
    CREATE TABLE person (first VARCHAR(32), last VARCHAR(32));
    CREATE TABLE thing (size VARCHAR(32), price VARCHAR(32));
EOF

Another would be to write the commands to a sufficiently protected (with permissions) temporary file and then pass that as a script to postgres with the -f FILENAME option.

answered Oct 16, 2012 at 5:29

paxdiablo

888k243 gold badges1.6k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

Note that you can use $variable substitutions in your here document, substituting shell variables into the SQL. If you don't want to do that you should usually quote the here-document to prevent substitution, eg <<'__EOF__' instead of <<__EOF__. Unexpected and exciting results arise when you use things like $1 or dollar-quoting in SQL without quoting your here-documents.

TraviJuu · Accepted Answer · 2012-10-16 12:05:25Z

2

#!/bin/bash

psql -d mydb -U postgres -c "CREATE TABLE person (first VARCHAR(32), last VARCHAR(32));"
psql -d mydb -U postgres -c "CREATE TABLE thing (size VARCHAR(32), price VARCHAR(32));"

You can run all queries with -c option.

answered Oct 16, 2012 at 12:05

TraviJuu

3765 silver badges12 bronze badges

1 Comment

user1749005 Over a year ago

Hey thank you so much man this is the method that I ended up using.

user1749005 · Accepted Answer · 2012-10-17 00:56:11Z

1

After editing the hba_conf file to "local all postgres trust" (I used trust so I wouldn't have to supply a password for my scripts), I used TraviJuu's suggestion and it now my script works flawlessly. Here is what the end result looks like.

!/bin/sh

sudo -u postgres createdb mydb

psql -w -d mydb -U postgres -c "CREATE TABLE person (first VARCHAR(32), last VARCHAR(32));"

psql -w -d mydb -U postgres -c "CREATE TABLE thing (size VARCHAR(32), price VARCHAR(32));"

exit 0;

answered Oct 17, 2012 at 0:56

user1749005

Collectives™ on Stack Overflow

How do I use SQL commands inside SH script

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related