This short guide explains how to connect to QuestDB using
Our implementation of the Postgres wire protocol is still in alpha. Some features such as metadata are not yet supported.
This guide assumes you have installed QuestDB and have it running.
How to Install psql
You can check if you already have psql installed with
Here are installation instructions for the major platforms:
brew install libpq brew link --force libpq
sudo apt-get install postgresql-client
sudo dnf install postgresql.x86_64
Install using the Windows Installer.
How to connect
The following shows how to connect to QuestDB over the Postgres wire protocol using the default configuration.
psql -h [host] -p [port] -U [user] -W -d [database]
|database||Default database is |
|host||Your IP. If running locally, that's |
|password||You will be prompted manually. Default is |
|port||Default is |
|user||Default is |
Establishing a connection
The following will use the default parameters to connect to a QuestDB instance running locally listening on port 8812.
psql -h localhost -p 8812 -U admin -W -d qdb
This will prompt you for a password. Enter the default password
successful connection will return the following
psql -h localhost -p 8812 -U admin -W -d qdb Password: psql (12.2, server 11.3) Type "help" for help. qdb=>
Using the command prompt
Now that you are connected to QuestDB, you can use the
psql prompt to run SQL
Let's create a simple table with three columns (timestamp, location and a temperature reading) with a designated timestamp.
CREATE TABLE temp( ts timestamp, location symbol, tempC double) timestamp(ts);
Symbol is a special type which allows us to write strings but store them efficiently as an int which makes writes and scans more efficient and reduces the storage requirements. Find out more here.
Let's simulate a house with 4 temperature sensors to populate our newly created table with test data.
We can insert data points manually as follows
INSERT INTO temp VALUES( systimestamp() , rnd_symbol('kitchen', 'bedroom', 'bathroom', 'garage'), round(rnd_int(10,15,0) + rnd_double(),1) );
Procedurally generated data
For the purpose of this guide, it is easier to insert from a file or in this
case to generate the data. We can use QuestDB's
row generation functions and
random generators to quickly create test
data. We use
long_sequence() which generates rows and returns a synthetic
x with monotonically increasing values. As
x is of type
cast to convert it to
The below will add 1 million readings from a location chosen at random approximatively every 30 seconds.
INSERT INTO temp SELECT dateadd('s', 30 * cast(x as int), systimestamp()) ts, rnd_symbol('kitchen', 'bedroom', 'bathroom', 'garage') location, round(rnd_int(10,15,0) + rnd_double(),1) tempC FROM long_sequence(1000000);
Now that we have data, we can run a few queries to start leveraging QuestDB's time-series SQL extensions.
Weekly average kitchen temperature over time
SELECT ts, avg(tempC) FROM temp WHERE location = 'kitchen' SAMPLE BY 7d;
This query uses SAMPLE BY to generate weekly time buckets in just 3 words.
Last temperature readings
SELECT * FROM temp LATEST BY location;
Last reading of december
SELECT * FROM temp LATEST BY location WHERE ts='2020-12';
There is plenty to do with QuestDB. As a next step, you could check out our guide to CRUD operations, how to join time-series with ASOF JOIN or how to FILL missing intervals within a select statement.
Before we leave, let's remember to cleanup and delete all the data
DROP TABLE temp;
We hope you enjoyed this guide. Feel free to join our Slack to ask questions if you would like to further explore a particular topic or ask functionality / syntax questions.