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

Question

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.

user_id is not null or nullable in your table?..

Vao Tsun
– Vao Tsun

2017-06-19 07:58:03 +00:00
Commented Jun 19, 2017 at 7:58 — Vao Tsun
– Vao Tsun, Commented Jun 19, 2017 at 7:58
Yeah user_id is always populated

Jared
– Jared

2017-06-19 18:53:35 +00:00
Commented Jun 19, 2017 at 18:53 — Jared
– Jared, Commented Jun 19, 2017 at 18:53

purushothaman poovai · Accepted Answer · 2020-02-10 08:33:22Z

5

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.

edited Feb 10, 2020 at 8:33

answered Feb 10, 2020 at 8:06

purushothaman poovai

4143 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2017-06-19 07:35:54Z

1

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).

answered Jun 19, 2017 at 7:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

6 Comments

Vao Tsun Over a year ago

without distinct delimiter bash will split values by spaces - I think this happens to the OP

Laurenz Albe Over a year ago

True; spaces in te result will make things much harder.

Laurenz Albe Over a year ago

But user_id doesn't sound like it contains spaces.

Vao Tsun Over a year ago

:) true! I have a habit to complicate things

Jared Over a year ago

So psql -Aqt -d is giving this output in the v2_ids variable (good so far): ac566b6cf34d495c9de4be1d81c42c56 ba8fb335223b4919a8287c963ecd9d85 But then read -a arr_ids_temp <<< $v2_ids ...is only adding the first one to the array. Getting close though.

|

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related