PostgreSQL - execute SQL query from file using command line

In this short article, we would like to show how to execute PostgreSQL query from SQL file using command line under Linux.

Quick solution:

psql postgres://my-username:my-password@localhost:5432/my-database ON_ERROR_STOP=1 --single-transaction -f /path/to/my.sql

Where:

• my-username and my-password should be replaced with current database credentials,
• localhost and 5432 should be used according to Postgres server address,
• my-database indicates the name of the database that we want to make SQL query,
• -v ON_ERROR_STOP=1 stops queries execution on error occurred,
• --single-transaction makes all queries located in a file in a single transaction (rollback on the error),
• -f /path/to/my.sql indicates the path to SQL file that we want to execute on my-database.

Note: do not forget to check if Postgres is installed or run the installation command:
sudo apt install postgresql postgresql-contrib - for Debian / Ubuntu Linuxes.

Usage example

In this section, we will show current database users printed from the command line.

/path/to/my.sql file:

SELECT * FROM "public"."users" LIMIT 1000;

Command line:

$ psql postgres://my-username:my-password@localhost:5432/my-database ON_ERROR_STOP=1 --single-transaction -f /path/to/my.sql
 id | username |       createdAt        |       updatedAt        
----+----------+------------------------+------------------------
  1 | john     | 2021-02-17 12:58:14+00 | 2021-02-17 12:58:19+00
  2 | chris    | 2021-02-17 12:58:33+00 | 2021-02-17 12:58:34+00
(2 rows)