Run SQL Files in psql Shell

When using PostgreSQL DBMS, you may need to run some SQL commands to populate a database or run some specific commands. You may also want to run these commands as a specific user. If that user doesn't use peer authentication, you'll have to run the SQL file in the psql shell. To learn how to achieve this, follow the steps below.

Step 1: Locate the SQL file

Locate the SQL file you want to run and copy its location. An example is given below:

/home/me/projects/run-sql-tut/list-databases.sql

me here is the current user's username. Replace it with your username.

Step 2: Log into the psql shell

sudo -iu postgres psql

Step 3: Execute the SQL file

\i /home/me/projects/run-sql-tut/list-databases.sql

This should give an output similar to

                               List of databases
    Name     |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-------------+----------+----------+---------+---------+-----------------------
 postgres    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0   | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
             |          |          |         |         | postgres=CTc/postgres
 template1   | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
             |          |          |         |         | postgres=CTc/postgres
(3 rows)