How to return a value from psql to bash and use it?

Question

Suppose I created a sequence in postgresql:

CREATE SEQUENCE my_seq;

I store the below line in an sql file get_seq.sql

SELECT last_value FROM my_seq;

$SUDO psql -q -d database_bame -f get_seq.sql

How do I get the int number returned by SELECT into bash and use it?

score 91 · Accepted Answer · edited Nov 08 '17 at 06:08

91

You can capture the result of a command using the VAR=$(command) syntax:

VALUE=$(psql -qtAX -d database_name -f get_seq.sql)
echo $VALUE

The required psql options mean:

-t only tuple

-A output not unaligned

-q quiet

-X Don't run .psqlrc file

edited Nov 08 '17 at 06:08

Eric Leschinski

answered Feb 11 '15 at 10:21

Tom-db

1

@eric-leschinski, why did you add the `-X` option? I think, this option in not needed in this case. – Tom-db Nov 08 '17 at 07:39
The `~/.psqlrc` file performs operations upon database login that dump noise to the output which is caught up into your `VALUE` variable, that noise was removed with the -X option. – Eric Leschinski Nov 08 '17 at 07:41
1

ok, this was you case, but is it generally valid? The psqlrc file could also perform operations which are needed for running the query – Tom-db Nov 08 '17 at 07:46

score 4 · Answer 2 · edited Apr 11 '22 at 02:21

4

Try:

LAST_VALUE=`echo "SELECT last_value FROM my_seq;" | psql -qAt -d database_name`

edited Apr 11 '22 at 02:21

Chris Johnson

answered Feb 11 '15 at 10:18

Lorenzo Marcon

2 Answers2