Skip to content

PostgreSQL: Store a PSQL COUNT query result as a Bash variable

G5th June 2018

CNo Comments

TBash, Perl, Postgres, SQL

Use this nifty code to isolate the result of a PSQL COUNT query as a variable within a Bash script.

In attempting to write a Bash script, I wanted to return the result of a COUNT function as a variable that I could then use as part of a follow-up table query. The COUNT function returns the number of rows that match a specific condition of a query.

In PSQL, my query looked like this:

SELECT COUNT (*) FROM analysis WHERE time_stamp >='2018-05-31' AND id LIKE '%B2018%'

Converting this query into a Bash script that will return the result as a variable is easy. Let’s see how it can be done:

#!/bin/bash
dbname="MyDatabase"
username="MyUsername"
MyQuery1=$( psql -X -A -U ${username} -d ${dbname} -c "SELECT COUNT (*) FROM analysis WHERE time_stamp >='2018-05-31' AND id LIKE '%B2018%'" )
MyQuery2=$( psql -X -A -U ${username} -d ${dbname} -t -c "SELECT COUNT (*) FROM analysis WHERE time_stamp >='2018-05-31' AND id LIKE '%B2018%'" )

Let’s generate some results:

echo "MyQuery1: "$MyQuery1
echo "MyQuery2: "$MyQuery2

There is a clear difference in the output between $MyQuery1 and $MyQuery2 in the formatting:

MyQuery1: count ------- 25 (1 row)
MyQuery2: 25

You’ll notice that the -t flag was used to generate the result for $MyQuery2 which ensured that only the tuple value was returned. In this case, $MyQuery2 can now be used elsewhere within the Bash script.

   

Comments:

No comments have yet been submitted. Be the first!

Have your say:

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.