About psql

The psql command line tool is widely used for interacting with a PostgreSQL or Timescale 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.

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

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

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

Copy

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

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

Copy

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

psql "postgres://tsdbadmin@<SERVICE_URL_WITH_PORT>/tsdb?sslmode=verify-full"

Copy

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:

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

Copy

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

• For a more comprehensive list of psql commands, see the Timescale psql cheat sheet.
• For more information about all psql commands, see the psql documentation.

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:

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

Copy

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.

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 :＋w＋q to save the changes, and return to the command prompt. Access the edited query by pressing ↑, and press Enter to run it.