How to execute a psql command within a bash for loop

Question

I want to execute a psql statement within a bash script and output the results to a file. The code I have below works as desired:

#!/bin/bash

query="select * from mytable;"

psql <<EOF > output.txt
\timing
$query
EOF

I want to run that psql command block 5 times and have the results appended to output.txt. It works fine if I just copy and paste it an additional 4 times, but when I try to put it inside of a for-loop, I get errors. Is there any way to do this?

This is the loop I tired:

#!/bin/bash

query="select * from mytable;"

for (( i=0; i<5; i++ ))
do
   psql <<EOF > output.txt
   \timing
   $query
   EOF
done

If I move the final EOF all the way over to the left, it only executes once, as if the loop wasn't there.

Replace > output.txt with >> output.txt.

codeforester
– codeforester

2017-03-07 22:14:06 +00:00
Commented Mar 7, 2017 at 22:14 — codeforester
– codeforester, Commented Mar 7, 2017 at 22:14

Community · Accepted Answer · 2017-05-23 12:00:21Z

6

You are overwriting the file each time with > inside the loop. You need >> inside or have > outside the loop:

#!/bin/bash

query="select * from mytable;"
for (( i=0; i<5; i++ ))
do
   psql <<EOF
   \timing
   $query
EOF
done > output.txt

Putting > after done is a little more efficient than >> inside the loop.

Comments

tripleee · Accepted Answer · 2019-01-24 07:25:05Z

0

It is usually better to not run Postgres in a loop. Just generate the commands you want to execute, then run the sequence of generated commands once.

#!/bin/bash

query="select * from mytable;"

for (( i=0; i<5; i++ ))
do
   cat <<___EOF
   \timing
   $query
___EOF
done |
psql > output.txt

which of course in this case can be simplified to just

#!/bin/bash
printf '-- %s\n\\timing\nselect * from mytable;\n' {1..5} |
psql >output.txt

The brace expansion {1..5} is Bash-specific, so you can't use sh for this particular snippet. (There is a difference.)

answered Jan 24, 2019 at 7:25

tripleee

192k37 gold badges318 silver badges367 bronze badges

Collectives™ on Stack Overflow

How to execute a psql command within a bash for loop

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related