[sql] PostgreSQL - query from bash script as database user 'postgres'

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.

This question is related to sql bash postgresql

The answer is


#!/bin/bash
password='complex=!password'
PGPASSWORD=$(echo $password) psql -h example.com -U example_user -d example_db -t -c "select * from example_table1" -o example_out.txt 

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

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

The safest way to pass commands to psql in a script is by piping a string or passing a here-doc.

The man docs for the -c/--command option goes into more detail when it should be avoided.

   -c command
   --command=command
       Specifies that psql is to execute one command string, command, and then exit. This is useful in shell scripts. Start-up files (psqlrc and ~/.psqlrc)
       are ignored with this option.

       command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single
       backslash command. Thus you cannot mix SQL and psql meta-commands with this option. To achieve that, you could pipe the string into psql, for
       example: echo '\x \\ SELECT * FROM foo;' | psql. (\\ is the separator meta-command.)

       If the command string contains multiple SQL commands, they are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands
       included in the string to divide it into multiple transactions. This is different from the behavior when the same string is fed to psql's standard
       input. Also, only the result of the last SQL command is returned.

       Because of these legacy behaviors, putting more than one command in the -c string often has unexpected results. It's better to feed multiple
       commands to psql's standard input, either using echo as illustrated above, or via a shell here-document, for example:

           psql <<EOF
           \x
           SELECT * FROM foo;
           EOF

To ans to @Jason 's question, in my bash script, I've dome something like this (for my purpose):

dbPass='xxxxxxxx'
.....
## Connect to the DB
PGPASSWORD=${dbPass} psql -h ${dbHost} -U ${myUsr} -d ${myRdb} -P pager=on --set AUTOCOMMIT=off

The another way of doing it is:

psql --set AUTOCOMMIT=off --set ON_ERROR_STOP=on -P pager=on \
     postgresql://${myUsr}:${dbPass}@${dbHost}/${myRdb}

but you have to be very careful about the password: I couldn't make a password with a ' and/or a : to work in that way. So gave up in the end.

-S


Once you're logged in as postgres, you should be able to write:

psql -t -d database_name -c $'SELECT c_defaults FROM user_info WHERE c_uid = \'testuser\';'

to print out just the value of that field, which means that you can capture it to (for example) save in a Bash variable:

testuser_defaults="$(psql -t -d database_name -c $'SELECT c_defaults FROM user_info WHERE c_uid = \'testuser\';')"

To handle the logging in as postgres, I recommend using sudo. You can give a specific user the permission to run

sudo -u postgres /path/to/this/script.sh

so that they can run just the one script as postgres.


In my case the best solution including the requirement for authentication is:

username="admin"
new_password="helloworld"

PGPASSWORD=DB_PASSWORD \
  psql -h HOSTNAME -U DB_USERNAME -d DATABASE_NAME -c \
  "UPDATE user SET password = '$new_password' WHERE username = '$username'"

This command will update a password of a user e.g. for recovery case.

Info: The trade-off here is that you need to keep in mind that the password will be visible in the bash history. For more information see here.

Update: I'm running the databse in a docker container and there I just need the commmand: docker exec -i container_name psql -U postgres -d postgres -c "$SQL_COMMAND"


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to bash

Comparing a variable with a string python not working when redirecting from bash script Zipping a file in bash fails How do I prevent Conda from activating the base environment by default? Get first line of a shell command's output Fixing a systemd service 203/EXEC failure (no such file or directory) /bin/sh: apt-get: not found VSCode Change Default Terminal Run bash command on jenkins pipeline How to check if the docker engine and a docker container are running? How to switch Python versions in Terminal?

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside