About psql

The psql command line tool is widely used for interacting with a PostgreSQL or TimescaleDB instance, and it is available for all operating systems. Most of the instructions in the Timescale documentation assume you are using psql.

To use psql to connect to your database, you need the connection details for your PostgreSQL server. For more information about how to retrieve your connection details, see the about connecting section.

Connecting to your database with psql

There are two different ways you can use psql to connect to your database.

You can provide the details using parameter flags, like this:

  1. psql -h <HOSTNAME> -p <PORT> -U <USERNAME> -W -d <DATABASENAME>

Alternatively, you can use a service URL to provide the details, like this:

  1. psql postgres://<USERNAME>@<HOSTNAME>:<PORT>/<DATABASENAME>?sslmode=require

If you configured your Timescale Cloud service to connect using SSL mode, use:

  1. psql "postgres://[email protected]<SERVICE_URL_WITH_PORT>/tsdb?sslmode=verify-full"

When you run one of these commands, you are prompted for your password. If you don’t want to prompted, you can supply your password directly within the service URL instead, like this:

  1. psql "postgres://<USERNAME>:<PASSWORD>@<HOSTNAME>:<PORT>/<DATABASENAME>?sslmode=require"

Common psql commands

When you start using psql, these are the commands you are likely to use most frequently:

CommandDescription
\c <DB_NAME>Connect to a new database
\d <TABLE_NAME>Show the details of a table
\dfList functions in the current database
\df+List all functions with more details
\diList all indexes from all tables
\dnList all schemas in the current database
\dtList available tables
\duList PostgreSQL database roles
\dvList views in current schema
\dv+List all views with more details
\dxShow all installed extensions
ef <FUNCTION_NAME>Edit a function
\hShow help on syntax of SQL commands
\lList available databases
\password <USERNAME>Change the password for the user
\qQuit psql
\setShow system variables list
\timingShow how long a query took to execute
\xShow expanded query results
\?List all psql slash commands

Save query results to a file

When you run queries in psql, the results are shown in the console by default. If you are running queries that have a lot of results, you might like to save the results into a comma-separated .csv file instead. You can do this using the COPY command. For example:

  1. \copy (SELECT * FROM ...) TO '/tmp/output.csv' (format CSV);

This command sends the results of the query to a new file called output.csv in the /tmp/ directory. You can open the file using any spreadsheet program.

Edit queries in a text editor

Sometimes, queries can get very long, and you might make a mistake when you try typing it the first time around. If you have made a mistake in a long query, instead of retyping it, you can use a built-in text editor, which is based on Vim. Launch the query editor with the \e command. Your previous query is loaded into the editor. When you have made your changes, press Esc, then type :wq to save the changes, and return to the command prompt. Access the edited query by pressing , and press Enter to run it.