How to Execute PostgreSQL Commands Inside Unix Shell Scripts

by SathiyaMoorthy on March 26, 2010

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 Sysadmin Course 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..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 5 comments… read them below or add one }

1 r March 26, 2010 at 4:26 am

What about PGPASSWORD ?

2 Hariharasubramanian March 26, 2010 at 6:00 am

One more way:

psql -U -c “select * from test”;

3 Rad Cirskis May 6, 2012 at 6:00 pm

You can take advantage of environment variables used by
http://www.postgresql.org/docs/9.1/static/libpq-envars.html , eg, PGDATABASE , PGUSER, PGPASSWORD etc.

Then you don’t need to provide psql parameters. It is useful if you have to run psql multiple times:

export PGDATABASE=test
export PGUSER=test
wherecond=”tgs”
psql << EOF
SELECT * FROM test WHERE col_name = '$wherecond';
EOF

4 subos September 12, 2012 at 7:29 am

That way the queue is printed in terminal window. How can it be “exported” to CSV (for example) ?

5 João Paulo June 6, 2013 at 9:32 am

psql << EOF
\copy (select * from table) to '/tmp/file.csv';
EOF

Leave a Comment

Previous post:

Next post: