Store PostgreSQL query result to Shell or PostgreSQL variable

Question

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)'

Answer 1

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.

Answer 2

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

Answer 3

var=`psql -Atc "select 1;"`
echo $var
1

Answer 4

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.

Answer 5

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)