How to execute multiple queries using psql command from bash shell?

Question

I need to execute postgresql queries from command line using psql -c command. For every psql command, it opens a new tcp connection to connect to the database server and execute query which is a overhead for large number of queries.

Currently I can execute single query like this:

psql -U postgres -h <ip_addr> -c "SELECT * FROM xyz_table;"

When I tried to execute multiple queries as below, but only the last query got executed.

psql -U postgres -h <ip_addr> -c "SELECT * FROM xyz_table; SELECT * FROM abc_table;"

Can anyone help me and tell me the proper way to do it?

Both statements are executed but only the last one returns as a result set. Check the manual: http://www.postgresql.org/docs/current/interactive/app-psql.html — Frank Heikens, Mar 02 '15 at 07:38
Thanks, @FrankHeikens! I think you should also add that as an answer (or propose an edit to the accepted answer). The top answer promotes the misinformation that only one command gets processed. — Wildcard, Oct 15 '16 at 00:41

score 82 · Accepted Answer · answered Mar 02 '15 at 06:16

82

-c processes only one command. Without it however psql expects commands to be passed into standard input, e.g.:

psql -U postgres -h <ip_addr> <database_name> << EOF
SELECT * FROM xyz_table;
SELECT * FROM abc_table;
EOF

Or by using echo and pipes.

answered Mar 02 '15 at 06:16

keltar

16,608
2
34
41

2

I also read about `psql -U postgres -h -f -` to do the same. Any pros or cons? – Vlastimil Ovčáčík Feb 19 '16 at 08:28
@VlastimilOvčáčík should be exactly the same. – keltar Feb 19 '16 at 09:44
2

As Wildcard noted in the question comment, executing with -c will actually execute all the statements, but only the result of last one will be returned. – viddik13 Jun 29 '17 at 12:37
1

Yes that nice but if i really need to execute psql two times, and keep the commands at the same session, is that possible? – Luciano Andress Martini Oct 19 '17 at 17:55
1

Current version of psql allows to use multiple `-c` commands. – Leo Y Nov 02 '20 at 05:32

score 20 · Answer 2 · answered Dec 26 '17 at 19:28

at least from 9.6.2 this approach works as well:

psql -c "select now()" -c "select version()" -U postgres -h 127.0.0.1

now

2017-12-26 20:25:45.874935+01 (1 row)

version

PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit (1 row)

score 18 · Answer 3 · answered May 15 '16 at 10:50

18

Using echo and a pipe to fit it on a single line:

echo 'SELECT * FROM xyz_table; \n SELECT * FROM abc_table' | psql -U postgres

answered May 15 '16 at 10:50

Salami

2,459
4
23
33

9

On some systems you may need to do `echo -e ...` – cbrown Feb 23 '17 at 14:36

score 14 · Answer 4 · answered Mar 02 '15 at 11:06

14

The --file parameter executes a file's content

psql -U postgres -h <ip_addr> -f "my_file.psql"

All the output will be sent to standard output

http://www.postgresql.org/docs/current/static/app-psql.html

answered Mar 02 '15 at 11:06

Clodoaldo Neto

108,856
25
211
247

This is best Option! – arshpreet Feb 17 '19 at 04:29

How to execute multiple queries using psql command from bash shell?

4 Answers4

now

version

Linked