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.

For command substitution you have to use either backticks (`) or $(). Single quotes (') won't do. — Ansgar Wiechers
– Ansgar Wiechers, Commented Mar 6, 2013 at 8: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
– knutella, Commented Mar 6, 2013 at 9:03
..somehow it swallows my backsticks in this coammands... but actually i added them before and after 2nd part of the assignment. — knutella
– knutella, Commented Mar 6, 2013 at 9:06
Possible duplicate of Store PostgreSQL query result to Shell or PostgreSQL variable — Francisco Puga
– Francisco Puga, Commented Jan 12, 2018 at 17:03

Kouber Saparev · Accepted Answer · 2022-02-11 12:17:44Z

94

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

edited Feb 11, 2022 at 12:17

answered Mar 6, 2013 at 9:53

Kouber Saparev

8,2552 gold badges32 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

knutella Over a year ago

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 Over a year ago

How to use dynamic bash script variable or psql variable in where condition

manny8086 Over a year ago

Thanks Kouber, is there any option to retain the new line ?

Ratnakri · Accepted Answer · 2018-01-17 07:47:00Z

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

edited Jan 17, 2018 at 7:47

answered Jan 17, 2018 at 6:46

Ratnakri

3093 silver badges4 bronze badges

2 Comments

Andrey Over a year ago

how to execute this command on a remote host? How to enter DB password?

chrismarx Over a year ago

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-

Collectives™ on Stack Overflow

store postgresql result in bash variable

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related