Connect to a Tiger Cloud service with psql

psql enables you to type in queries interactively, issue them to Postgres, and see the query results. Connect to your Tiger Cloud service using psql

psql is a terminal-based frontend to 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 .

Prerequisites

To follow the steps on this page:

• Create a target with time-series and analytics enabled.

  You need your connection details. This procedure also works for .

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

Install psql

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

MacOS Homebrew

Install using Homebrew. libpqxx is the official C++ client API for .

Connect to your

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

Connect to your with either:

• The parameter flags:

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

• The URL:

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

  You are prompted to provide the password.

• The 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 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 psql cheat sheet and psql documentation.

Save query results to a file

When you run queries in psql, the results are shown in the terminal 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_columnstore'
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.