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

Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..