PostgreSQL - query from bash script as database user 'postgres'
bash, postgresql, sql
Solution
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'"
Problem
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.