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

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.

Have Your Say

Δ