48

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
  • 193,178
  • 25
  • 254
  • 328
knutella
  • 1,223
  • 2
  • 12
  • 12
  • 1
    For command substitution you have to use either backticks (`\``) or `$()`. Single quotes (`'`) won't do. – Ansgar Wiechers Mar 06 '13 at 08:56
  • 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 Mar 06 '13 at 09:03
  • ..somehow it swallows my backsticks in this coammands... but actually i added them before and after 2nd part of the assignment. – knutella Mar 06 '13 at 09:06
  • Possible duplicate of [Store PostgreSQL query result to Shell or PostgreSQL variable](https://stackoverflow.com/questions/12535766/store-postgresql-query-result-to-shell-or-postgresql-variable) – Francisco Puga Jan 12 '18 at 17:03

2 Answers2

89

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'"`
Kouber Saparev
  • 7,637
  • 2
  • 29
  • 26
  • 3
    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';") – knutella Mar 06 '13 at 10:04
  • How to use dynamic bash script variable or psql variable in where condition – Sherin Green Jan 12 '22 at 12:54
15

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
Ratnakri
  • 309
  • 3
  • 4
  • 1
    how to execute this command on a remote host? How to enter DB password? – Andrey Apr 26 '20 at 08:49
  • 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- – chrismarx Jun 16 '21 at 20:49