psql command line tool is widely used for interacting with a PostgreSQL or
TimescaleDB instance, and it is available for all operating systems. Most of
the instructions in the Timescale documentation assume you are using
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>
Alternatively, you can use a service URL to provide the details, like this:
If you configured your Timescale Cloud service to connect using SSL mode, use:
psql "postgres://[email protected]<SERVICE_URL_WITH_PORT>/tsdb?sslmode=verify-full"
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:
When you start using
psql, these are the commands you are likely to use most
|Connect to a new database|
|Show the details of a table|
|List functions in the current database|
|List all functions with more details|
|List all indexes from all tables|
|List all schemas in the current database|
|List available tables|
|List PostgreSQL database roles|
|List views in current schema|
|List all views with more details|
|Show all installed extensions|
|Edit a function|
|Show help on syntax of SQL commands|
|List available databases|
|Change the password for the user|
|Show system variables list|
|Show how long a query took to execute|
|Show expanded query results|
|List all |
- For a more comprehensive list of
psqlcommands, see the Timescale psql cheat sheet.
- For more information about all
psqlcommands, 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
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
/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
q to save the changes, and return to the command prompt. Access the
edited query by pressing
↑, and press
Enter to run it.
Found an issue on this page?Report an issue!