Tuesday, November 26, 2019

How to execute a *.sql file using psql in a PostgreSQL database



There are two ways:

1. Already connected to the correct database:
postgres=# \connect proddb01
You are now connected to database "proddb01" as user "postgres".
proddb01=# \conninfo
You are connected to database "proddb01" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
proddb01=#

Then use the -i notation in psql to execute your file, like this:
proddb01=# \i yourfile.sql

2. directly from the command line:
proddb01=# psql proddb01 -f yourfile.sql

You can also direct the output to a log file, using the -o flag:
proddb01=# psql proddb01 -f test.sql -o test.log

The -o flag will redirect everything to a log file and not show anything on your screen.
If you would like to see the result of the script on your screen AND save the output in a log file, use the -L flag instead of the -o flag. This will also log the actual query sent to the postgres server:
proddb01=# psql proddb01 -f test.sql -L test.log

Note that no "exit" statement is necessary in your .sql scripts that you execute through psql directly from the command line. psql quits the session automatically.

No comments:

Post a Comment