Run PSQL queries stored as strings in bash

Question

I'm trying to do a script that runs queries in PostgreSQL. I'm struggling with something that seems pretty simple (but does not work...).

I have a set of queries stored as an array of strings, lets' say

query1="SELECT COUNT(id) FROM nodes WHERE label='KeywordList';"
query2="SELECT COUNT(id) FROM nodes WHERE label='AuthorList';"
queries=($query1 $query2)

Then, I want to run this query in PSQL through a loop:

for query in "${queries[@]}" 
do
    psql $db_name -c "$query"
done

This gives me the following error:

ERROR:  syntax error at or near "COUNT"
LINE 1: COUNT(id)
        ^
ERROR:  syntax error at or near "FROM"
LINE 1: FROM
        ^
ERROR:  syntax error at or near "nodes"
LINE 1: nodes
        ^
ERROR:  syntax error at or near "WHERE"
LINE 1: WHERE
        ^
ERROR:  syntax error at or near "label"
LINE 1: label='KeywordList';

But this works when I give to PSQL the string itself: psql $db_name -c "$query1" (this returns me 10).

I think that this is a quoting problem, or maybe Bash tries to understand what is in the string... I can not resolve this on my own after many tries with double, single and back quotes.

Help will be really appreciated, Nelly.

score 1 · Accepted Answer · answered Nov 25 '20 at 15:46

As is:

$ query1="SELECT COUNT(id) FROM nodes WHERE label='KeywordList';"
$ query2="SELECT COUNT(id) FROM nodes WHERE label='AuthorList';"
$ queries=($query1 $query2)
$ typeset -p queries
declare -a queries=([0]="SELECT" [1]="COUNT(id)" [2]="FROM" [3]="nodes" [4]="WHERE" [5]="label='KeywordList';" [6]="SELECT" [7]="COUNT(id)" [8]="FROM" [9]="nodes" [10]="WHERE" [11]="label='AuthorList';")

Notice the queries have been broken into separate tokens and stored as separate array entries.

As with your psql example that works, you need to quote the query variables when referencing them ... to include when populating the array, eg:

$ queries=("$query1" "$query2")
$ typeset -p queries
declare -a queries=([0]="SELECT COUNT(id) FROM nodes WHERE label='KeywordList';" [1]="SELECT COUNT(id) FROM nodes WHERE label='AuthorList';")

glad I could help; `typeset -p ` is a **BIG** help when trying to figure out what exactly is stored in a variable (especially arrays). — markp-fuso, Nov 25 '20 at 15:51

Run PSQL queries stored as strings in bash

1 Answers1