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.