How to Execute PostgreSQL Commands Inside Unix Shell Scripts

Question: How do I executed PostgreSQL Commands inside a Linux / UNIX shell script?

Answer: With the help of the psql interactive terminal, you can execute the psql commands from the shell script. For this purpose, you should enable the password less login by pg_hba.conf, or .pgpass.

Syntax

psql DBNAME USERNAME << EOF
     statement 1;
     statement 2;
     .
     .
     statement n;
EOF

PostgreSQL: Executing SQL from shell scripts

Provide all the postgreSQL commands between the EOF block as shown below.

#!/bin/sh

dbname="test"
username="test"
psql $dbname $username << EOF
SELECT * FROM test;
EOF

PostgreSQL: Using variables in SQL from shell scripts

You can also use shell script variable inside the EOF block as shown below.

#!/bin/sh

dbname="test"
username="test"
wherecond="tgs"
psql $dbname $username << EOF
SELECT * FROM test WHERE col_name = '$wherecond';
EOF