Right, so you’ve graduated from typing SQL directly into psql one line at a time. Welcome to the big leagues. We’re now going to talk about running entire files of SQL, which is how you do real work: loading data, applying schema changes, running complex reports. You have two main ways to do this, and while they seem similar, the devil—as always—is in the details.

The In-Command (\i) and The Flag (-f)

You’ll use \i (for “include”) when you’re already inside a psql session and you suddenly remember you were supposed to run that migration script. Its cousin is the -f flag, which you use when you launch psql from your normal shell prompt.

Think of it like this: \i is for when you’re already in the car and decide to add a new destination to the trip. The -f flag is for when you get in the car and tell the GPS your destination before you even start the engine.

Here’s the \i command in its natural habitat:

-- Inside your psql session
postgres=# \i /path/to/my/brilliant_schema.sql

This reads the file and executes its contents as if you had painstakingly typed it all by hand, error-prone fingers and all.

The -f flag is for one-and-done operations from your shell:

psql -d my_database -f /path/to/my/brilliant_schema.sql

This connects to my_database, runs the file, and—crucially—exits immediately afterward. This is what you want for automation in shell scripts, CI/CD pipelines, and cron jobs. If you used \i in a script, it would fail because \i is a meta-command, not something your shell understands.

The Path Kerfuffle

This is where everyone gets tripped up at least once. psql has a concept of a “current path” for file operations, and it’s not what you think.

When you use \i inside psql, it does not use your shell’s current working directory. It uses its own. You can see what this is by running \! pwd (which shells out to run the pwd command). This internal path is usually the directory you were in when you started the psql session.

This leads to the classic frustration:

postgres=# \i my_script.sql
my_script.sql: No such file or directory

You’re screaming, “But it’s right there!” To psql, it is not. You have two options: use the absolute path (/home/you/my_script.sql) or the relative path from psql’s current directory. You can change psql’s current directory with the \cd meta-command. Yes, it’s a bit janky.

The -f flag, used from the shell, doesn’t have this problem because you’re using your shell’s native path resolution. So psql -f my_script.sql will work just fine if the file is in your shell’s current directory.

The Quiet Death of Single-Statement Files

Here’s a subtle one that has burned me more times than I care to admit. If your SQL file contains only one statement and you forget the terminating semicolon, \i will run it just fine. psql sees the end of the file and implicitly terminates the statement.

But if your file has more than one statement and you forget the semicolon on the first one… well, let’s just say you’re in for a bad time. psql will read the second statement as a continuation of the first, resulting in a glorious, incomprehensible parse error. The lesson: always terminate your statements with a semicolon. It’s not a suggestion. It’s the law. The file-based equivalent of this:

-- This will cause chaos and despair
CREATE TABLE my_table (id int)
INSERT INTO my_table VALUES (1)

-- This is the way
CREATE TABLE my_table (id int);
INSERT INTO my_table VALUES (1);

Using Variables with \i

This is where \i gets a secret superpower. You can use psql’s variable interpolation within your SQL files. It’s incredibly useful for scripting.

-- Inside psql
postgres=# \set my_schema 'production'
postgres=# \i run_report.sql

And inside run_report.sql:

SELECT * FROM :my_schema.user_table;

When psql processes the \i, it replaces :my_schema with the string production before sending the SQL to the server. This is wildly useful for environment-agnostic scripts. You can’t do this with -f alone; you’d have to combine it with shell environment variables and psql’s -v flag, which is a whole other chapter of fun.