psql Command Line Tool - PostgreSQL Terminal Interface Guide - databases Cheatsheets

psql is the PostgreSQL terminal interface. This guide provides essential commands for interacting with PostgreSQL databases directly from your command line. The following commands were tested on version 9.5.

PostgreSQL Connection Options

Before executing commands, you might need to specify connection details. Common options include:

-U username: Specifies the PostgreSQL username. If not provided, the current operating system user is used.
-p port: Sets the port number for the database connection.
-h server hostname/address: Defines the server where the PostgreSQL instance is running.

Connecting to a Specific Database

To establish a connection to a particular PostgreSQL database, use the following command structure:

psql -U <username> -h <host> -d <database>

Listing Available Databases

To view all databases available on a PostgreSQL server, you can use the --list option:

psql -U <username> -h <host> --list

Executing SQL Queries and Saving Output

You can execute a single SQL query and save its output to a file using the -c and -o flags:

psql -U <username> -d <database> -c 'select * from tableName;' -o <outfile>

Getting Tabular HTML Output

For queries that require HTML formatted output, use the -H flag:

psql -U <username> -d <database> -H -c 'select * from tableName;'

Exporting Query Results to CSV

To save query results into a CSV file, leverage the COPY command within psql. If you do not need column names in the first row, remove the word header:

psql -U <username> -d <database> -c 'copy (select * from tableName) to stdout with csv header;' -o <outfile>

Executing Commands from a File

For more complex operations or scripts, you can read commands from a file using the -f option:

psql -f <outfile>

Restoring Databases from a File

To restore a PostgreSQL database from a backup file, use the -f option with the backup file path and the target username:

psql -f <outfile> <username>

External Resources