store postgresql result in bash variable

Question

How to atore a scalar postgresql-value on a bash-variable like in script below?

dbname="testlauf" username="postgres"  vartest='psql -c -d $dbname -U $username -h localhost -p 5432 "SELECT gid FROM testtable WHERE aid='1';"' echo "$vartest" 

I tried several different writings, but nothing seems to work. Thanks in advance.

Answer 1

Put the -c option just before its argument - the query. Mind also using the additional -t option to get just the tuple value. And of course, use the backticks (`) operator.

Using the -X option is also recommended, as sometimes a .psqlrc file might add some redundant output, as well as the -A option, which disables column aligning (whitespaces).

In order to skip NOTICE or other additional messages, include the -q flag.

vartest=`psql -d $db -U $user -AXqtc "SELECT gid FROM testtable WHERE aid='1'"` 

Answer 2

Using -t option or --tuples-only will give you the rows only, so it will easier to store them in array variable (if the result from query more than one)

vartest =(`psql -t -d $dbname -U $username -c "SELECT gid FROM testtable WHERE aid='1';"`) echo $vartest 

example:

query result

ubuntu@ratnakri:~$ psql -h localhost -p 5432 -t -U postgres -d postgres -c "select slot_name from pg_replication_slots" barman barman2 

make it into array variable

    ubuntu@ratnakri:~$ RESULT=(`psql -h localhost -p 5432 -t -U postgres -d postgres -c "select slot_name from pg_replication_slots"`)     ubuntu@ratnakri:~$ echo ${RESULT[0]}     barman     ubuntu@ratnakri:~$ echo ${RESULT[1]}     barman2