Languages
[Edit]
EN

PostgreSQL - execute SQL query from file using command line

4 points
Created by:
Root-ssh
99280

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)
Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

â€ïžđŸ’» 🙂

Join