For instance, I have a table stores value:
select * from myvalue;
val
-------
12345
(1 row)
How can I save this 12345 into a variable in postgresql or shell script?
Here's what I tried in my shell script:
var=$(psql -h host -U user -d db <<SQLSTMT
SELECT * FROM myvalue;
SQLSTMT)
but echo $var gives me:
val ------- 12345 (1 row)
I've also tried
\set var (select * from myvalue)
in psql and when I type \set it lists:
var = '(select*frommyvalue)'
No, no, no! Use "raw data" switch from psql, like "-t" or "\t" and pipe the query to psql instead of parsing ascii-table, come on :-)
echo 'select * from myvalue;' | psql -t -h host -U user -d db
If you really need parse psql output, you could also use -H switch ( turns on HTML output ), and parse it with some perl module for parsing html tables, I used that once or twice.. Also, you may want to use a pgpass file and ~/.psqlrc for some defaults, like default DB to connect, when not specified.
psql has a -c/--command= option to accept SQL from the command line, and -t/--tuples-only option to control output formatting.
$ psql -c 'select 1+1'
?column?
----------
2
(1 row)
$ psql -t -c 'select 1+1'
2
$ VALUE=`psql -t -c 'select 1+1'`
$ echo $VALUE
2
In this answer I explain one way to do it, using a co-process to communicate back-and-forth with psql. That's overkill if all you need is to run a query and get a single result, but might be good to know if you're shell scripting with psql.
You can filter the result you get with your psql command:
var=$(psql -h host -U user -d db <<SQLSTMT
SELECT * FROM myvalue;
SQLSTMT)
var=$(cut -d' ' -f3 <<<$var)
None of these worked for me, but this did:
median_avm=psql "host=${dps1000} port=#### dbname=@@@ user=${reduser} password=${redpass}" -c "SELECT AVG(column) FROM db.table;" -t
using a source file with ${dps1000}, ${reduser}, ${redpass} defined and manually entering port and dbname
