PostgreSQL - query from bash script as database user 'postgres'

Try this one:

#!/bin/bashpsql -U postgres -d database_name -c "SELECT c_defaults  FROM user_info WHERE c_uid = 'testuser'"

Or using su:

#!/bin/bashsu -c "psql -d database_name -c \"SELECT c_defaults  FROM user_info WHERE c_uid = 'testuser'\"" postgres

And also sudo:

#!/bin/bashsudo -u postgres -H -- psql -d database_name -c "SELECT c_defaults  FROM user_info WHERE c_uid = 'testuser'"

You can connect to psql as below and write your sql queries like you do in a regular postgres function within the block. There, bash variables can be used. However, the script should be strictly sql, even for comments you need to use -- instead of #:

#!/bin/bashpsql postgresql://<user>:<password>@<host>/<db> << EOF       <your sql queries go here>EOF

if you are planning to run it from a separate sql file. here is a good example (taken from a great page to learn how to bash with postgresql http://www.manniwood.com/postgresql_and_bash_stuff/index.html

#!/bin/bashset -eset -uif [ $# != 2 ]; then   echo "please enter a db host and a table suffix"   exit 1fiexport DBHOST=$1export TSUFF=$2psql \  -X \  -U user \  -h $DBHOST \  -f /path/to/sql/file.sql \  --echo-all \  --set AUTOCOMMIT=off \  --set ON_ERROR_STOP=on \  --set TSUFF=$TSUFF \  --set QTSTUFF=\'$TSUFF\' \   mydatabase   psql_exit_status = $?   if [ $psql_exit_status != 0 ]; then     echo "psql failed while trying to run this sql script" 1>&2     exit $psql_exit_status   fi   echo "sql script successful"exit 0