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

In this quick guide, you will learn 2 methods to run a SQL script (.sql file) in Postgres and also how to write the script output to a file in the database host.

How to run SQL script from psql prompt

Login to your Postgres database:

psql -U username

Once you logged in to psql prompt, you can use the \i option to execute a script. It takes the script path as the input and executes it.

\i /root/test.sql

This command will execute the /root/test.sql script and prints the results to standard out.

To redirect the output to a file instead, you can use \o option before you execute the script. The \o option sets an output file and creates it if it does not exist. It takes the ouput file path as the parameter. Example:

\o /root/output.txt

Then run the script like we did earlier:

\i /root/test.sql

You will not see any output because the output was directed to the output.txt file.

Check the content of that file and you will see the output of your query.

If you specified \o and run multiple scripts or statements from psql prompt, the output for all of your statements will be appended to the output file you specified.

Moreover, the \o sets the output temporarily which means once you jumped out of the psql prompt, it will not be in use anymore.

How to run SQL script from the host terminal

If you wish to execute the script right from the database host terminal without first logging in to Postgres, you can do so with -f option. For example:

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

And if you wish to also redirect its output to a file, you can use -o option.

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

This will execute the script and write its ouput to a file.

Conclusion

You can use \i and \o to execute a SQL script in Postgres and redirect its ouput to a file.

You can also use the oneliner from your database host terminal using -f and -o option to execute the script and write the ouput to a file respectively.

In this tutorial, we covered the two easy methods of executing SQL scripts in Postgress and how to write the output to a file.