Query & SQL Overview

Querying - as a base action - is performed in three primary ways:

  1. Query via the QuestDB Web Console
  2. Query via PostgreSQL
  3. Query via REST HTTP API

QuestDB provides SQL with enhanced time series extensions.

This makes analyzing, downsampling, processing and reading time series data an intuitive and flexible experience.

Queries can be written into many applications using the many rich and diverse drivers and clients of the PostgreSQL or REST-ful ecosystems. However, querying is also leveraged heavily by third-party tools to provide visualizations, such as within Grafana, or for connectivity into broad data infrastructure and application environments such as with a tool like Cube.

Need to ingest data first? Checkout our Ingestion overview.

QuestDB Web Console#

The Web Console is available by default at localhost:9000. The GUI makes it easy to write, return and chart queries. There is autocomplete, syntax highlighting, errors, and more. If you want to test a query or interact direclty with your data in the cleanest and simplest way, apply queries via the Web Console.

A shot of the Web Console, showing auto complete and a colourful returned table.
Click to zoom

PostgreSQL#

Query QuestDB using the PostgreSQL endpoint via the default port 8812.

Examples in multiple languages are shown below.

import psycopg as pg
import time
# Connect to an existing QuestDB instance
conn_str = 'user=admin password=quest host=127.0.0.1 port=8812 dbname=qdb'
with pg.connect(conn_str, autocommit=True) as connection:
# Open a cursor to perform database operations
with connection.cursor() as cur:
#Query the database and obtain data as Python objects.
cur.execute('SELECT * FROM trades_pg;')
records = cur.fetchall()
for row in records:
print(row)
# the connection is now closed

PostgreSQL compatibility#

QuestDB offers broad compatibility with the PostgreSQL ecosystem.

However, please note some limitations.

Forward-only cursors#

QuestDB diverges from PostgreSQL in its handling of cursor commands. While PostgreSQL supports scrollable cursors, enabling backward and forward navigation through the results of a SQL query, QuestDB applies a different approach.

QuestDB does not support scrollable cursors that require explicit creation and management through DECLARE CURSOR and subsequent operations like FETCH. Instead, QuestDB supports non-scrollable, or "forward-only", cursors. This distinction means that while you can iterate over query results sequentially, you cannot navigate backwards or access result positions as you might with scrollable cursors in PostgreSQL.

As a result, some PostgreSQL drivers and libraries that rely on scrollable cursors may not be fully compatible with QuestDB. For instance, psycopg2 โ€” a popular PostgreSQL driver for Python โ€” utilizes scrollable cursors extensively. If possible, select drivers that support non-scrollable cursors for optimal compatibility. One such example is asyncpg, which is database driver for asyncio and PostgreSQL.

REST HTTP API#

QuestDB exposes a REST API for compatibility with a wide range of libraries and tools.

The REST API is accessible on port 9000 and has the following query-capable entrypoints:

For details such as content type, query parameters and more, refer to the REST HTTP API reference.

EntrypointHTTP MethodDescriptionREST HTTP API Reference
/exp?query=..GETExport SQL Query as CSVReference
/exec?query=..GETRun SQL Query returning JSON result setReference

/exp: SQL Query to CSV#

The /exp entrypoint allows querying the database with a SQL select query and obtaining the results as CSV.

For obtaining results in JSON, use /exec instead, documented next.

curl -G --data-urlencode \
"query=SELECT * FROM example_table2 LIMIT 3" \
http://localhost:9000/exp
"col1","col2","col3"
"a",10.5,true
"b",100.0,false
"c",,true

/exec: SQL Query to JSON#

The /exec entrypoint takes a SQL query and returns results as JSON.

This is similar to the /exp entry point which returns results as CSV.

Querying Data#
curl -G \
--data-urlencode "query=SELECT x FROM long_sequence(5);" \
http://localhost:9000/exec

The JSON response contains the original query, a "columns" key with the schema of the results, a "count" number of rows and a "dataset" with the results.

{
"query": "SELECT x FROM long_sequence(5);",
"columns": [{ "name": "x", "type": "LONG" }],
"dataset": [[1], [2], [3], [4], [5]],
"count": 5
}

Alternatively, the /exec endpoint can be used to create a table and the INSERT statement can be used to populate it with values:

# Create Table
curl -G \
--data-urlencode "query=CREATE TABLE IF NOT EXISTS trades(name STRING, value INT)" \
http://localhost:9000/exec
# Insert a row
curl -G \
--data-urlencode "query=INSERT INTO trades VALUES('abc', 123456)" \
http://localhost:9000/exec
# Update a row
curl -G \
--data-urlencode "query=UPDATE trades SET value = 9876 WHERE name = 'abc'" \
http://localhost:9000/exec

What's next?#

Now... SQL! It's query time.

SQL is fairly intuitive and known to many.

Whether you want to use the Web Console, PostgreSQL or REST HTTP (or both), query construction is rich.

To brush up and learn what's unique in QuestDB, consider the following:

And to learn about some of our favourite, most powerful syntax:

  • Window functions are a powerful analysis tool
  • Aggregate functions - aggregations are key!
  • Date & time operators to learn about date and time
  • SAMPLE BY to summarize data into chunks based on a specified time interval, from a year to a microsecond
  • WHERE IN to compress time ranges into concise intervals
  • LATEST ON for latest values within multiple series within a table
  • ASOF JOIN to associate timestamps between a series based on proximity; no extra indices required

Looking for visuals?


โญ Something missing? Page not helpful? Please suggest an edit on GitHub.