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

• Connecting to a Different Host: To connect to a remote PostgreSQL server, use -h for the host and -p for the port.

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.