Connect to Tiger Cloud with psql

Connect to your Tiger Cloud service and run interactive queries 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

To follow the procedure on this page you need to:

Create a target Tiger Cloud service.

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:

Linux/MacOS
Windows

psql --version

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:

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

Install Homebrew, if you don’t already have it:
Terminal window
```
/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.
Make sure your Homebrew repository is up to date:
Terminal window
```
brew doctor
brew update
```
Install psql:
Terminal window
```
brew install libpq
```
Update your path to include the psql tool:
Terminal window
```
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.

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

Install MacPorts by downloading and running the package installer.
Make sure MacPorts is up to date:
Terminal window
```
sudo port selfupdate
```
Install the latest version of libpqxx:
Terminal window
```
sudo port install libpqxx
```
View the files that were installed by libpqxx:
Terminal window
```
port contents libpqxx
```

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

Make sure your apt repository is up to date:
Terminal window
```
sudo apt-get update
```
Install the postgresql-client package:
Terminal window
```
sudo apt-get install postgresql-client
```

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

Download and run the PostgreSQL installer from www.enterprisedb.com.
In the Select Components dialog, check Command Line Tools, along with any other components you want to install, and click Next.
Complete the installation wizard to install the package.

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:
Terminal window
```
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.