Skip to main content
0 reactions

How to Run a SQL File in psql

Featured card titled Run a SQL File in psql, with a terminal window showing hello.sql, \i hello.sql, the result INSERT 0 2 and -f count.sql.
Run every statement in a SQL file with psql.

Typing long SQL by hand is slow, and one typo means typing it again. Save the SQL in a file instead, and psql runs every statement in it for you.

Every command and output on this page is real, from PostgreSQL 19 on macOS.

Syntax

Syntax (psql)Plain text
\i filename
Syntax (Terminal)Bash
psql -f filename
  • \i: reads the file and runs it as though you’d typed it, without leaving psql.
  • -f: starts psql, runs the file and quits.
  • filename: the file’s name. psql looks for it in the folder you started psql from, or you can give its full path.

Both are described in the psql documentation (opens in a new tab).

Step 1: Create an Example Database

This guide runs its files in a small database named filedemo. Connect with psql, as Part 2 of the PostgreSQL Tutorial shows, and run:

postgres=#SQL
CREATE DATABASE filedemo;
OutputPlain text
CREATE DATABASE

Then type \q to go back to Terminal.

Step 2: Write a SQL File

With any plain-text editor, save a file named hello.sql in your home folder with these three statements:

hello.sqlSQL
CREATE TABLE greetings (id integer, message text);
INSERT INTO greetings VALUES (1, 'Hello'), (2, 'Bonjour');
SELECT * FROM greetings;

The first line creates a small table, the second adds two rows to it, and the third shows them.

Step 3: Run the File with \i

Open Terminal, which starts in your home folder, and connect to the example database:

TerminalBash
psql -U postgres -d filedemo

Then run the file:

filedemo=#Plain text
\i hello.sql
OutputPlain text
CREATE TABLE
INSERT 0 2
 id | message
----+---------
  1 | Hello
  2 | Bonjour
(2 rows)

Each statement printed its own result: the table was created, two rows were inserted, and the SELECT showed them.

Step 4: Run a File from Terminal with -f

Save a second file, count.sql, in your home folder:

count.sqlSQL
SELECT count(*) FROM greetings;

Back in Terminal, run it without opening psql first:

TerminalBash
psql -U postgres -d filedemo -f count.sql
OutputPlain text
Password for user postgres:
 count
-------
     2
(1 row)

psql connected, ran the file, printed the result and quit.

Clean Up

To remove the example database, connect as usual and run:

postgres=#SQL
DROP DATABASE filedemo;
OutputPlain text
DROP DATABASE

Warning: DROP DATABASE deletes the database and everything in it at once. There’s no undo, so check the name before you press Return.

Then delete hello.sql and count.sql if you don’t need them.

Common Mistakes

MistakeWhat happensDo this instead
Running a file that isn’t in the folder psql started fromnosuch.sql: No such file or directoryStart psql from the file’s folder, or give the file’s full path.
A typo inside the filepsql:bad.sql:1: ERROR: syntax error at or near "SELEC" LINE 1: SELEC * FROM greetings;The message names the file and the line (bad.sql:1). Fix that line and run the file again.

FAQ

Does psql stop at the first error in a file?

Not by default: it reports the error and carries on with the next statement. To stop at the first error, set the ON_ERROR_STOP variable, as in psql -v ON_ERROR_STOP=1 -f hello.sql, which the psql documentation (opens in a new tab) describes.

What’s the difference between \i and -f?

\i runs a file inside the psql session you already have open, and you stay in psql afterwards. -f starts psql just to run the file, then quits, which suits scripts.

Conclusion

Save your SQL in a file, then run it with \i filename inside psql or psql -f filename from Terminal. psql runs every statement in order and names the file and line of any error.

Additional Resources

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.

0 comments