How to execute a SQL script (.sql file) in Postgres

This guide provides two methods for executing SQL scripts (.sql files) in PostgreSQL and details how to redirect script output to a file.

Executing SQL Scripts from the psql Prompt

To begin, log in to your PostgreSQL database using the psql command-line tool:

psql -U username

Once logged in, you can execute a script using the \i command, which takes the path of the script file as its input:

\i /root/test.sql

This command will execute the script and display the results in the terminal.

To save the script output to a file, use the \o option before executing the script. This command sets the output file path and creates the file if it does not exist:

\o /root/output.txt

Next, run the script as before:

\i /root/test.sql

The script output will be written to /root/output.txt. Note that if you execute multiple scripts or statements after setting the \o option, all outputs will be appended to the specified file. The \o setting is temporary and will be reset once you exit the psql prompt.

Executing SQL Scripts from the Host Terminal

You can execute a SQL script directly from the host terminal without logging into psql by using the -f option:

psql -U your_username -d your_database_name -f /path/to/script.sql

To redirect the script output to a file, use the -o option along with the -f option:

psql -U your_username -d your_database_name -o /path/to/output.txt -f /path/to/script.sql

This command will execute the script and write the output to output.txt.

Conclusion

In this guide, we covered two methods for executing SQL scripts in PostgreSQL and redirecting their output to a file:

1. Using the psql prompt with \i and \o commands.
2. Executing directly from the host terminal with -f and -o options.