i.h4d35 i.h4d35 - 5 months ago 33
SQL Question

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.

Answer

Try this one:

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

Or

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

Another way:

#!/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'"
Comments