How To Run a SQL File With psql

The Short Answer

$ psql -U user_name -d database_name < file.sql

psql is a command-line tool that enables you to connect to a PostgreSQL instance and interactively administer it by running SQL commands. These commands are used to manage users, roles, databases, and databases objects such as tables, views, indexes, etc., by performing CRUD (i.e. Create, Read, Update, Delete) operations against them.

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.

In this post, we’ll discuss how to import and execute a SQL file into a PostgreSQL database using the psql command, and how to resolve the most common errors that might occur when doing so.

Importing a SQL file using the psql command

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_name -d database_name < file.sql

The second method for importing a SQL file is to use the -f option 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_name -d database_name -f file.sql

In both cases:

• The -U option flag is used to specify the name of the user under which the SQL commands will be executed.
• The -d option flag is used to specify the name of the database these commands will be executed on.

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

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

psql> ALTER USER user_name WITH PASSWORD ‘new_password’;

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;

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

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.