How to Execute Multiple Queries Using psql Command From Bash

1. Overview

psql is one of the most popular text-based frontends for PostgreSQL. It allows users to interact with a PostgreSQL server using SQL queries and meta-commands.

While we can leverage psql to execute a single SQL query interactively, we can also utilize it in Bash to execute multiple queries successively. This method comes in handy if we have a significantly high number of queries to execute, saving time over executing them interactively.

In this tutorial, we’ll discuss a few methods to execute multiple queries using the psql command from Bash.

2. Executing Multiple Queries From Bash

Now, let’s see various ways we can execute multiple SQL queries using the combination of Bash and psql.

2.1. Using a Pipe

One of the easiest methods to execute multiple queries is using the combination of the echo command and a pipe:

$ echo 'select user; select current_date' | psql -h localhost -U postgres
   user   
----------
 postgres
(1 row)

 current_date 
--------------
 2025-02-16
(1 row)

Here, we can see that the SQL query prints the current user and date.

2.2. Using the -c Option

Similarly, we can use the -c option of the psql command to specify multiple queries:

$ psql -h localhost -U postgres -c "select user" -c "select current_date"
   user   
----------
 postgres
(1 row)

 current_date 
--------------
 2025-02-16
(1 row)

We should note that the -c option runs only a single command and exits, therefore we must use a separate -c flag for each query we want to execute.

2.3. Using the Here Document

Additionally, we can also use the Bash’s Here Document to specify multiple queries in a single block:

$ psql -U postgres -h localhost << EOF
select user;
select current_date;
EOF

In this example, the << EOF at the end of the first line represents the start of the Here Document, whereas the EOF at the last line represents the end of the Here Document.

2.4. Using a Script File

So far, we’ve provided SQL queries directly from the standard input stream. However, this method isn’t very efficient if the queries are complex. In such cases, we can store the queries in an SQL file and execute them successively with the -f option. Let’s understand this with a simple example.

To begin, let’s create the multiple_queries.sql file with the following content:

select user;
select current_date;

Next, let’s use the -f option of the psql command to run the queries from the multiple_queries.sql file:

$ psql -h localhost -U postgres -f multiple_queries.sql
   user   
----------
 postgres
(1 row)

 current_date 
--------------
 2025-02-16
(1 row)

Both of the commands in our script file were executed.

Similarly, we can also use Bash’s input redirection operator to achieve the same result:

$ psql -h localhost -U postgres < multiple_queries.sql
   user   
----------
 postgres
(1 row)

 current_date 
--------------
 2025-02-16
(1 row)

This method produced the same result as using the -f option in the previous example.

3. Conclusion

In this article, we discussed executing multiple queries using the psql command from the Bash shell. Using any of these methods will allow us to execute multiple queries from within the Bash shell, and the one we choose simply depends on our preference and requirements.