CODEWITHSUNDEEP
bashpostgresql
Jared
Jared

Reputation: 111

Storing results from a Postgres query directly into an array in Bash 4?

I'm trying to run a Postgres query like below and store the results into an array:

v2_ids=$(psql $(credstash get database/path/here) -tc "select distinct(user_id) from table where yada yada yada..." )

read -a arr_ids_temp <<< $v2_ids

Is there a better way to do this? It seems that read -a only grabs the first result sometimes and I'm not sure why.

Upvotes: 3

Views: 4899

Answers (2)

purushothaman poovai
purushothaman poovai

Reputation: 384

Refer the following code:

IFS=$'\n' res=(`psql -tA database username -c 'select column_name from table_name where blah blah...'`)
  • We must tell the bash to store the result in array based on '\n' delimiter.
  • psql options -tA for removing header/footer etc and removing leading and trailing blanks.

${res[0]} contains value of first row and each element in array contains the value of each row respectively.

Additional tips:

For taking all data in a row into array,

IFS='|' res=(`psql -tA database username -c 'select name1,name2,name3 from table_name where blah blah...'`)
  • Here I told bash to store the psql result in array based on | delimiter.

Upvotes: 5

Laurenz Albe
Laurenz Albe

Reputation: 247765

That should work fine, but use psql with the options -A (unaligned output mode), -q (suppress informational output) and -t (no column names, headers and footers).

Upvotes: 1

Related Questions