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 [.inline-code]<[.inline-code] which causes a program to read from a file instead of the standard input.

When used, the [.inline-code]psql[.inline-code] 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>

Where:

• [.inline-code]user[.inline-code] is the name of the user under which the SQL file will be executed.
• [.inline-code]database[.inline-code] is the name of the database the SQL commands will be executed on.
• [.inline-code]file[.inline-code] is the path to the SQL file..

For example, this command will execute the [.inline-code]dataset.sql[.inline-code] file on the [.inline-code]development[.inline-code] database as the [.inline-code]admin[.inline-code] user:

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

[#import-sql-file-using-psql] Importing a SQL file using the psqlcommand [#import-sql-file-using-psql]

The second method for importing a SQL file is to use the [.inline-code]psql[.inline-code] command with the [.inline-code]-f[.inline-code] flag, which will cause the [.inline-code]psql[.inline-code] 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>

[#easily-recall-syntax-with-ai] Easily retrieve this command using Warp’s AI Command Suggestions [#easily-recall-syntax-with-ai]

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

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

[#fix-common-errors] Fixing common errors [#fix-common-errors]

Importing a SQL file using the [.inline-code]psql[.inline-code] 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.

[#the-invalid-password-error] invalid_password: Invalid password [#the-invalid-password-error]

The [.inline-code]invalid_password[.inline-code] 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 [.inline-code]du[.inline-code] command:

psql> \du

Or change the user’s password using the [.inline-code]ALTER USER[.inline-code] command:

psql> ALTER USER user_name WITH PASSWORD ‘new_password’;

[#the-insufficient-privileges-error]insufficient_privilege: Insufficient privileges [#the-insufficient-privileges-error]

The [.inline-code]insufficient_privilege[.inline-code] 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 [.inline-code]GRANT[.inline-code] command.

psql> GRANT ALL ON  table_name TO user_name;

[#the-undefined-file-error] undefined_file: File not found [#the-undefined-file-error]

The [.inline-code]undefined_file[.inline-code] 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.

[#the-permission-denied-error] Permission denied [#the-permission-denied-error]

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 [.inline-code]ls -l[.inline-code] command, and update them using the [.inline-code]chmod[.inline-code] command.

For example:

$ chmod a+r file.sql

[#the-syntax-error] syntax_error: Syntax error [#the-syntax-error]

The [.inline-code]syntax_error[.inline-code] 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.

[#the-duplicate-table-error] duplicate_table: Table already exists [#the-duplicate-table-error]

The [.inline-code]duplicate_table[.inline-code] 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 [.inline-code]DROP TABLE[.inline-code] command before re-running your SQL file, or using the [.inline-code]CREATE TABLE IF NOT EXISTS[.inline-code] command in your SQL file to skip this command if the table already exists.