How to run SQL files in a psql shell

When using PostgreSQL DBMS, you may need to run some SQL commands to populate a database or run 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 do this, follow the steps below.

Step 1: Locate the SQL file

Locate the SQL file you want to run and copy its location. See the following example.

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

me 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)