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

Question

I have a table in my PostgreSQL database which has 3 columns - c_uid, c_defaults and c_settings. c_uid simply stores the name of a user and c_defaults is a long piece of text which contains a lot of data w.r.t that user.

I have to execute a statement from a bash script which selects the value of the c_defaults column based on the c_uid value and this needs to be done by the database user 'postgres'.

On the CLI I can do the following:

[mymachine]# su postgres
bash-4.1$psql
postgres=#\c database_name
You are now connected to database "database_name" as user "postgres".
database_name=#SELECT c_defaults  FROM user_info WHERE c_uid = 'testuser';

However, how do I achieve this through a bash script?

The aim is to get the information from that column, edit it and write it back into that column - all through a bash script.

Answer 1

Try this one:

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

Or using su:

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

And also sudo:

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

Answer 2

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/bash
psql postgresql://<user>:<password>@<host>/<db> << EOF
       <your sql queries go here>
EOF

Answer 3

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/bash
set -e
set -u
if [ $# != 2 ]; then
   echo "please enter a db host and a table suffix"
   exit 1
fi

export DBHOST=$1
export TSUFF=$2
psql \
  -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