CODEWITHSUNDEEP
databasepostgresqlsh
user1749005
user1749005

Reputation:

How do I use SQL commands inside SH script

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).

Upvotes: 1

Views: 305

Answers (3)

user1749005
user1749005

Reputation:

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;

Upvotes: 1

TraviJuu
TraviJuu

Reputation: 376

#!/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.

Upvotes: 2

paxdiablo
paxdiablo

Reputation: 881113

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.

Upvotes: 3

Related Questions