Tutorial: How to install psql on Mac, Ubuntu, Debian, Windows

    Before you start

    Before you start, you should confirm that you don’t already have psql installed. In fact, if you’ve ever installed Postgres or TimescaleDB before, you likely already have psql installed.

    First, install the . Homebrew simplifies the installation of software on macOS.

    Second, update brew. From your command line, run the following commands:

    1. brew doctor
    2. brew update
    3. brew install libpq

    Finally, create a symbolic link to psql (and other libpq tools) into /usr/local/bin so that you can reach it from any command on the macOS Terminal.

    1. brew link --force libpq ail

    Install on Ubuntu 16.04,18.04 and Debian 9,10

    Install on Ubuntu and Debian using the apt package manager:

    We recommend using the installer from .

    Last step: Connect to your PostgreSQL server

    Let’s confirm that psql is installed:

    1. psql --version

    Now, in order to connect to your PostgreSQL server, you’ll need the following connection parameters:

    • Hostname
    • Port
    • Username
    • Password

    There are two ways to use these parameters to connect to your PostgreSQL database.

    Option 1: Supply parameters at the command line

    In this method, use parameter flags on the command line to supply the required information to connect to a PostgreSQL database:

    Option 2: Use a service URI

    The Service URI begins with postgres://.

    Common psql commands

    Here is a table of common commands you’ll find yourself using a lot:

    Save results of a query to a comma-separated file

    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 command:

    1. \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.

    Edit a SQL query in an editor

    Sometimes you may find yourself writing a lengthy query such as this one from our Hello Timescale! tutorial:

    1. -- For each airport: num trips, avg trip duration, avg cost, avg tip, avg distance, min distance, max distance, avg number of passengers
    2. SELECT rates.description, COUNT(vendor_id) AS num_trips,
    3.    AVG(dropoff_datetime - pickup_datetime) AS avg_trip_duration, AVG(total_amount) AS avg_total,
    4.    AVG(tip_amount) AS avg_tip, MIN(trip_distance) AS min_distance, AVG (trip_distance) AS avg_distance, MAX(trip_distance) AS max_distance,
    5.    AVG(passenger_count) AS avg_passengers
    6.  FROM rides
    7.  JOIN rates ON rides.rate_code = rates.rate_code
    8.  WHERE rides.rate_code IN (2,3) AND pickup_datetime < '2016-02-01'
    9.  ORDER BY rates.description;

    Congrats! Now you have connected via .