Connect to Tiger Cloud 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 Tiger Cloud service.

Prerequisites for this integration guide

To follow these steps, you'll need:

• A Tiger Cloud service.

  These steps use Tiger Cloud, but the same approach applies to a self-hosted TimescaleDB instance.

• Your connection details.

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:

Linux/MacOS

psql --version

Windows

wmic
/output:C:\list.txt product get name, version

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:

MacOS Homebrew

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

1. Install Homebrew if you don't already have it

   /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

   For more information about Homebrew, including installation instructions, see the Homebrew documentation.

2. Update your Homebrew repository

   brew doctor
   brew update

3. Install psql

   brew install libpq

4. Update your path to include the psql tool

   brew link --force libpq

On Intel chips, the symbolic link is added to /usr/local/bin. On Apple Silicon, the symbolic link is added to /opt/homebrew/bin.

MacOS MacPorts

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

1. Install MacPorts

   Install MacPorts by downloading and running the package installer.

2. Update MacPorts

   sudo port selfupdate

3. Install the latest version of libpqxx

   sudo port install libpqxx

4. View the files installed by libpqxx

   port contents libpqxx

Debian and Ubuntu

Install psql on Debian and Ubuntu with the apt package manager.

1. Update your apt repository

   sudo apt-get update

2. Install the postgresql-client package

   sudo apt-get install postgresql-client

Windows

psql is installed by default when you install PostgreSQL. This procedure uses the interactive installer provided by PostgreSQL and EnterpriseDB.

1. Download and run the installer

   Download and run the PostgreSQL installer from www.enterprisedb.com.

2. Select Command Line Tools

   In the Select Components dialog, check Command Line Tools, along with any other components you want to install, and click Next.

3. Complete the installation wizard

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 Tiger Data 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.