current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
2

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.

share|improve this question

2 Answers 2

up vote 6 down vote accepted

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'"
share|improve this answer
    
I ended up using the second one option that you've given and passed it into a variable. Thank you. –  i.h4d35 Aug 14 '13 at 5:58
up vote 2 down vote

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.

share|improve this answer
    
-bash-4.1$ psql -t -d database_name $'SELECT c_defaults FROM user_info WHERE c_uid = \'testuser\';' gives me and error - "psql: FATAL: role "SELECT c_defaults FROM user_info WHERE c_uid = 'testuser';" does not exist " –  i.h4d35 Aug 14 '13 at 5:32
    
@i.h4d35: Oops, sorry, I'd left out the -c flag (introducing the query or command for psql to run). I've added it now. –  ruakh Aug 14 '13 at 5:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.