How To Run A PSQL File With psql

While most database operations can be performed manually, it is sometimes necessary to automate them using SQL files. For example, when replicating an existing database structure and its objects, or migrating thousands of database entries into another PostgreSQL instance.

The short answer

The first method for importing a SQL file in PostgreSQL is to use the input redirection operator < which causes a program to read from a file instead of the standard input.

When used, the psql command will behave as if the commands contained in the SQL file were manually entered by a user through the command-line interface.

$ psql -U <user> -d <database> < <file>

Run in Warp

Where:

• user is the name of the user under which the SQL file will be executed.
• database is the name of the database the SQL commands will be executed on.
• file is the path to the SQL file..

For example, this command will execute the dataset.sql file on the development database as the admin user:

$ psql -U admin -d development < dataset.sql

Run in Warp

Importing a SQL file using the psqlcommand

The second method for importing a SQL file is to use the psql command with the -f flag, which will cause the psql command to behave the same way as with the previous method, while enabling additional features such as error messages with line numbers.

$ psql -U <user> -d <database> -f <file>

Run in Warp

Easily retrieve this command using Warp’s AI Command Suggestions

If you’re using Warp as your terminal, you can easily retrieve this command using the Warp AI Command Suggestions feature:

Entering import sql file psql in the AI Command Suggestions will prompt a psql command that can then be quickly inserted in your shell by doing CMD+ENTER .

Fixing common errors

Importing a SQL file using the psql command might fail for various reasons.

Here is a list of the most common errors you might encounter, and that you will need to resolve before the file can successfully be imported.

invalid_password: Invalid password

The invalid_password error indicates that you entered the wrong password for the specified user.

If the error persists after several attempts, you can connect to the PostgreSQL instance using an account with elevated privileges, and verify that the specified user exists using the du command:

psql> \du

Run in Warp

Or change the user’s password using the ALTER USER command:

psql> ALTER USER user_name WITH PASSWORD ‘new_password’;

Run in Warp

insufficient_privilege: Insufficient privileges

The insufficient_privilege error indicates that the specified user doesn’t have the necessary privileges to perform the actions contained in the SQL file.

You can solve this by connecting into the PostgreSQL instance using an account with elevated privileges, and granting a different set of privileges to the specified user using the GRANT command.

psql> GRANT ALL ON  table_name TO user_name;

Run in Warp

undefined_file: File not found

The undefined_file error indicates that the file you are trying to import doesn’t exist.

You can solve this by making sure that the file actually exists, and that the provided file path is valid.

Permission denied

This error indicates that the provided PostgreSQL user doesn’t have permission to read the file you are trying to import.

You can verify the file’s permissions using the ls -l command, and update them using the chmod command.

For example:

$ chmod a+r file.sql

Run in Warp

syntax_error: Syntax error

The syntax_error error indicates that the SQL file contains one or more syntax errors.

You can solve this by either referring to the official PostgreSQL documentation or by testing your file using an online syntax checker.

duplicate_table: Table already exists

The duplicate_table error indicates that you are trying to create a database table that already exists.

You can solve this by either first dropping (i.e. permanently deleting) the existing table using the DROP TABLE command before re-running your SQL file, or using the CREATE TABLE IF NOT EXISTS command in your SQL file to skip this command if the table already exists.