How to Run a PostgreSQL .sql File from the Command Line?
Run a PostgreSQL .sql File using command line arguments
In PostgreSQL, you can execute SQL scripts stored in .sql files directly from the command line using the psql command-line tool. This is especially useful for running complex queries or setting up database structures automatically.
Syntax to Run a .sql File with psql
The general syntax for running a .sql file using psql is:
Syntax:
psql -U username -d database_name -f /path/to/your/file.sql
Explanation:
- -U username: Specifies the username for connecting to the database.
- -d database_name: Specifies the database where the script will be executed.
- -f /path/to/your/file.sql: Provides the path to the SQL file to execute.
Example Code:
Suppose you have a file named setup.sql containing table creation statements and initial data inserts. You want to run this file on a PostgreSQL database called my_database using a user named postgres.
# Run setup.sql file on my_database as postgres user
psql -U postgres -d my_database -f /path/to/setup.sql
Explanation:
- psql -U postgres: Launches the psql command-line tool and connects as the postgres user.
- -d my_database: Specifies my_database as the target database for the SQL script.
- -f /path/to/setup.sql: Executes all SQL commands within setup.sql on my_database.
Additional Command Line Options:
- Prompting for Password: If you need to enter a password, you can add the -W option to force a password prompt.
psql -U postgres -d my_database -W -f /path/to/setup.sql
psql -U postgres -h remote_host -p 5432 -d my_database -f /path/to/setup.sql
Using \i Command within psql
If you’re already connected to a database within psql, you can run a .sql file using the \i command.
\i /path/to/setup.sql
This method allows you to run SQL files without having to exit and re-enter psql.
Important Notes:
- File Path: Use the absolute path to the .sql file for ease, especially if running the command from a different directory.
- Permissions: Ensure the user specified has sufficient permissions to execute the commands in the SQL file on the specified database.
- Error Handling: Any errors during script execution will be shown in the terminal. To log output to a file, consider redirecting stdout and stderr.
All PostgreSQL Questions, Answers, and Code Snippets Collection.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/snippets/run-a-postgresql-sql-file-using-command-line-arguments.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics