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.

• Ansgar Wiechers: For command substitution you have to use either backticks (`) or $(). Single quotes (') won't do.
• knutella: Thanks. But even vartest=$(psql -c -d testlauf -U postgres -h localhost -p 5432 "SELECT gid FROM testtable WHERE aid='1';") won't do the trick unfortunatelly... it gives me "sytnac error near or at "-d"" I also tried it with dbname...
• knutella: ..somehow it swallows my backsticks in this coammands... but actually i added them before and after 2nd part of the assignment.
• Francisco Puga: Possible duplicate of Store PostgreSQL query result to Shell or PostgreSQL variable

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'"`

• knutella: Thanks a lot Kouber, this does the trick! Actually backticks still don't seem to work for me, but ($) does: vartest=$(psql -X -h localhost -p 5432 -d testlauf -U postgres -c "SELECT gid FROM testtable WHERE aid='1';")
• Sherin Green: How to use dynamic bash script variable or psql variable in where condition
• manny8086: Thanks Kouber, is there any option to retain the new line ?

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

• Andrey: how to execute this command on a remote host? How to enter DB password?
• chrismarx: There are several ways to execute on remote host, but for db password, by default postgresql is setup to use peer authentication on unix, just don't specify the -h localhost to use it-