Connect with psql

psql is a terminal-based frontend to PostgreSQL that enables you to type in queries interactively, issue them to Postgres, and see the query results.

This page shows you how to use the psql command line tool to interact with your Timescale Cloud service.

Prerequisites

Before integrating:

Create a target Timescale Cloud service. You need your connection details to follow this procedure.
This procedure also works for self-hosted TimescaleDB.

Check for an existing installation

On many operating systems, psql is installed by default. To use the functionality described in this page, best practice is to use the latest version of psql. To check the version running on your system:

If you already have the latest version of psql installed, proceed to the Connect to your service section.

Install psql

If there is no existing installation, take the following steps to install psql:

Connect to your service

To use psql to connect to your service, you need the connection details. See Find your connection details.

Connect to your service with either:

The parameter flags:


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

The service URL:


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

You are prompted to provide the password.

The service URL with the password already included and a stricter SSL mode enabled:


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

Useful psql commands

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

Command	Description
`\c <DB_NAME>`	Connect to a new database
`\d <TABLE_NAME>`	Show the details of a table
`\df`	List functions in the current database
`\df+`	List all functions with more details
`\di`	List all indexes from all tables
`\dn`	List all schemas in the current database
`\dt`	List available tables
`\du`	List PostgreSQL database roles
`\dv`	List views in current schema
`\dv+`	List all views with more details
`\dx`	Show all installed extensions
`ef <FUNCTION_NAME>`	Edit a function
`\h`	Show help on syntax of SQL commands
`\l`	List available databases
`\password <USERNAME>`	Change the password for the user
`\q`	Quit `psql`
`\set`	Show system variables list
`\timing`	Show how long a query took to execute
`\x`	Show expanded query results
`\?`	List all `psql` slash commands

For more on psql commands, see the Timescale psql cheat sheet and psql documentation.

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:


\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.

Run long queries

To run multi-line queries in psql, use the EOF delimiter. For example:


psql -d $TARGET -f -v hypertable=<hypertable> - <<'EOF'
SELECT public.alter_job(j.id, scheduled=>true)
FROM _timescaledb_config.bgw_job j
JOIN _timescaledb_catalog.hypertable h ON h.id = j.hypertable_id
WHERE j.proc_schema IN ('_timescaledb_internal', '_timescaledb_functions')
AND j.proc_name = 'policy_compression'
AND j.id >= 1000
AND format('%I.%I', h.schema_name, h.table_name)::text::regclass = :'hypertable'::text::regclass;
EOF

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

Related Content