psql is the standard command line interface for interacting with a PostgreSQL
or TimescaleDB instance. Here we explain how to install
psql on various platforms.
Before you start, you should confirm that you don’t already have
In fact, if you’ve ever installed Postgres or TimescaleDB before, you likely already
If your client doesn't have
psql installed, find the appropriate section below
for OS specific instructions on how to install it.
First, install the Brew Package Manager. Homebrew simplifies the installation of software on macOS.
brew. From your command line, run the following commands:
brew doctor brew update brew install libpq
Finally, create a symbolic link to
psql (and other
libpq tools) into
so that you can reach it from any command on the macOS Terminal.
brew link --force libpq ail
Install on Ubuntu and Debian using the
apt package manager:
sudo apt-get update sudo apt-get install postgresql-client
psqlclient and not the PostgreSQL database.
We recommend using the installer from PostgreSQL.org.
In order to connect to your PostgreSQL server, you’ll need the following connection parameters:
There are two ways to use these parameters to connect to your PostgreSQL database.
In this method, use parameter flags on the command line to supply the required information to connect to a PostgreSQL database:
psql -h HOSTNAME -p PORT -U USERNAME -W -d DATABASENAME
Once you run that command, the prompt will ask you for your password. (This is the purpose
The Service URI begins with
Here is a table of common commands you'll find yourself using a lot:
|List available databases|
|Connect to a new database|
|List available tables|
|Describe the details of given table|
|List all schemas in the current database|
|List functions in the current database|
|Get help on syntax of SQL commands|
|Lists all |
|System variables list|
|Shows how long a query took to execute|
|Show expanded query results|
You may often find yourself running SQL queries with lengthy results. You can save these
results to a comma-separated file (CSV) using the
\copy (SELECT * FROM ...) TO '/tmp/myoutput.csv' (format CSV);
You would then be able to open
/tmp/myoutput.csv using any spreadsheet or similar
program that reads CSV files.
Sometimes you may find yourself writing a lengthy query such as this one below:
-- For each airport: num trips, avg trip duration, avg cost, avg tip, avg distance, min distance, max distance, avg number of passengers SELECT rates.description, COUNT(vendor_id) AS num_trips, AVG(dropoff_datetime - pickup_datetime) AS avg_trip_duration, AVG(total_amount) AS avg_total, AVG(tip_amount) AS avg_tip, MIN(trip_distance) AS min_distance, AVG (trip_distance) AS avg_distance, MAX(trip_distance) AS max_distance, AVG(passenger_count) AS avg_passengers FROM rides JOIN rates ON rides.rate_code = rates.rate_code WHERE rides.rate_code IN (2,3) AND pickup_datetime < '2016-02-01' GROUP BY rates.description ORDER BY rates.description;
It would be pretty common to make an error the first couple of times you attempt to
write something that long in SQL. Instead of re-typing every line or character,
you can launch a
vim editor using the
\e command. Your previous command can
then be edited, and once you save ("Escape-Colon-W-Q") your edits, the command will
appear in the buffer. You will be able to get back to it by pressing the up arrow
in your Terminal window.
Congrats! Now you have connected via
Found an issue on this page?Report an issue!