Executing SQL Scripts with psql

PostgreSQL’s psql command-line tool is a powerful way to interact with your databases. A common task is executing SQL scripts containing multiple statements, such as INSERTs, CREATE TABLEs, or complex queries. This tutorial will cover the various ways to execute these scripts using psql.

Understanding psql

psql is an interactive terminal for running SQL commands. However, it can also execute SQL scripts non-interactively, which is essential for automation and loading large datasets.

Basic Script Execution

The most straightforward method to execute a SQL script is using the -f option, which specifies the file containing the SQL commands.

psql -f your_script.sql

This command will connect to the default PostgreSQL server (usually localhost) using the default user (your operating system user) and database (also your operating system user). It then executes all the SQL statements within your_script.sql.

Specifying Database, User, and Host

For more control, you’ll often need to specify the database, user, and host. Here’s how:

• -d database_name: Specifies the database to connect to.
• -U username: Specifies the user to connect as.
• -h host: Specifies the host name or IP address of the PostgreSQL server.

Example:

psql -h localhost -d mydatabase -U myuser -f your_script.sql

This connects to the mydatabase database on the local machine (localhost) as the user myuser and executes your_script.sql.

Password Authentication

When using a specific user (-U), you’ll likely be prompted for a password. You can avoid being prompted by using one of several methods:

1. Environment Variables: Set the PGPASSWORD environment variable. Caution: This is generally considered less secure, as the password is visible in the environment.

   export PGPASSWORD=your_password
   psql -h localhost -d mydatabase -U myuser -f your_script.sql
   

2. .pgpass File: Create a .pgpass file in your home directory with the following format:

   hostname:port:database:username:password
   

   For example:

   localhost:5432:mydatabase:myuser:your_password
   

   Make sure the file has restricted permissions: chmod 600 ~/.pgpass.

3. pg_hba.conf Configuration: Modify the pg_hba.conf file (usually located in the PostgreSQL data directory) to trust connections from specific users and hosts. This is a more advanced option and requires careful consideration of security implications. Caution: using trust authentication can compromise security if not configured properly.

Alternative Syntax within psql

If you are already connected to the psql interactive terminal, you can execute scripts using the following commands:

• \i path_to_sql_file: Executes the script located at the absolute path.
• \ir path_to_sql_file: Executes the script located at the relative path from your current directory.

Quiet Mode

If you want to suppress the echoing of SQL statements and output during script execution, use the -q (quiet) option.

psql -q -f your_script.sql

This is useful when you are running the script as part of an automated process and don’t need the verbose output.

Combining Options

You can combine multiple options to tailor the script execution to your specific needs. For example:

psql -h myhost -d mydb -U myuser -q -f /path/to/script.sql

This connects to the database on myhost as myuser, executes /path/to/script.sql in quiet mode, and uses the password provided (either through environment variable, .pgpass file, or prompt).