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: postgresql.org/docs/current/interactive/app-psql.html — Frank Heikens
– Frank Heikens, Commented Mar 2, 2015 at 7: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
– Wildcard, Commented Oct 15, 2016 at 0:41

keltar · Accepted Answer · 2015-03-02 06:16:06Z

104

-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 2, 2015 at 6:16

keltar

18.5k3 gold badges40 silver badges46 bronze badges

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

7 Comments

Vlastimil Ovčáčík Over a year ago

I also read about psql -U postgres -h <ip_addr> <database_name> -f - to do the same. Any pros or cons?

keltar Over a year ago

@VlastimilOvčáčík should be exactly the same.

viddik13 Over a year ago

As Wildcard noted in the question comment, executing with -c <command> will actually execute all the statements, but only the result of last one will be returned.

Luciano Andress Martini Over a year ago

Yes that nice but if i really need to execute psql two times, and keep the commands at the same session, is that possible?

Leo Y Over a year ago

Current version of psql allows to use multiple -c commands.

|

toraritte · Accepted Answer · 2023-04-19 22:22:33Z

Specify each command with consecutive -c flags:

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

The output of the above command:

              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)

Salami · Accepted Answer · 2016-05-15 10:50:47Z

26

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, 2016 at 10:50

Salami

3,1474 gold badges28 silver badges35 bronze badges

2 Comments

cbrown Over a year ago

On some systems you may need to do echo -e ...

tripleee Over a year ago

@cbrown On those systems (too), you should almost certainly prefer printf over echo -e

Clodoaldo Neto · Accepted Answer · 2015-03-02 11:06:30Z

20

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 2, 2015 at 11:06

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

1 Comment

arshpreet Over a year ago

This is best Option!

hraban · Accepted Answer · 2023-03-13 19:51:23Z

0

The "clean" solution is to pass multiple -c options:

$ psql ... -c 'select 1 as a' -c "select 'foo' as b"
 a
---
 1
(1 row)

  b
-----
 foo
(1 row)

Notable benefit: you can use backslash commands this way, e.g. \x.

(@leo-y points this out in a comment)

answered Mar 13, 2023 at 19:51

hraban

2,2281 gold badge22 silver badges31 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

7 Comments

Comments

2 Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

Comments

2 Comments

1 Comment

Comments

Linked

Related