how to insert PostgreSQL result to a shell variable

Question

I want to set all this syntax in variable

su -l postgres -c "psql -c \"CREATE DATABASE graphite WITH OWNER graphite\""
CREATE DATABASE 

so I wrote this

res=$( su -l postgres -c "psql -c \"CREATE DATABASE graphite WITH OWNER graphite\"" )
CREATE DATABASE 

but $res is empty

echo $res

I also tried to add " " but without success.

How to insert the results of

su -l postgres -c "psql -c \"CREATE DATABASE graphite WITH OWNER graphite\""

to a shell variable?

• Laurenz Albe: It works for me (PG v10 on Linux bash).
• King David: please show me what you do ( which syntax ? )
• King David: pg_config --version PostgreSQL 9.2.13
• Vao Tsun: do you run it as root?.. I mean could it be that you wait for password or smth else instead of reading stdout?..
• Laurenz Albe: As user root: x=$(su -l postgres -c "psql -c \"CREATE TABLE test (id integer)\"")

Answer 1

checking the resulting exit state works:

MacBook-Air:~ root# res=$(su -l vao -c "/usr/local/Cellar/postgresql/9.6.1/bin/psql -c \"CREATE DATABASE graphite\" -d so")
MacBook-Air:~ root# echo $?
0
MacBook-Air:~ root# res=$(su -l vao -c "/usr/local/Cellar/postgresql/9.6.1/bin/psql -c \"CREATE DATABASE graphite\" -d so")
ERROR: database "graphite" already exists
MacBook-Air:~ root# echo $?
1

and the stdout shows as well:

MacBook-Air:~ root# echo $res
Timing is on. Pager usage is off. SET Time: 0.333 ms SET Time: 0.112 ms SET Time: 0.127 ms Time: 0.290 ms

Answer 2

This is a more generic answer, but backticks (`) will put the results of any shell command into a variable:

$ foo=`psql -d postgres -c "select 'Hello'"`
$ echo $foo
?column? ---------- Hello (1 row)

That said, I'm curious why you would want the results of a create database command in a shell variable. I'm sure there's a good reason, I just never would think to do such a thing.

• King David: var=` su -l postgres -c "psql -c \"CREATE DATABASE graphite WITH OWNER graphite\"" ` and echo$var , still give null value
• Hambone: Admittedly, I didn't do it with the su -l postgres but I did do a create database, and it returned the results info the variable. Interesting. Which Linux are you on?
• Vao Tsun: backtick is old way - I think $() is preferred for bash nowadays. I'm not bash expert though...
• Hambone: @VaoTsun -- I had no idea -- I wasn't even familar with that syntax. Thanks for the feedback.
• Vao Tsun: @Hambone no worries - here are some reference: mywiki.wooledge.org/BashFAQ/082