SQL extensions

QuestDB attempts to implement standard ANSI SQL. We also try to be compatible with PostgreSQL, although parts of this are a work in progress. This page presents the main extensions we bring to SQL and the main differences that one might find in SQL but not in QuestDB's dialect.

SQL extensions

We have extended SQL to support our data storage model and simplify semantics of time series analytics.

LATEST ON

LATEST ON is a clause introduced to help find the latest entry by timestamp for a given key or combination of keys as part of a SELECT statement.

LATEST ON customer ID and currency

SELECT * FROM balances

WHERE balance > 800

LATEST ON ts PARTITION BY customer_id, currency;

SAMPLE BY

SAMPLE BY is used for time-based aggregations with an efficient syntax. The short query below will return the simple average balance from a list of accounts by one month buckets.

SAMPLE BY one month buckets

SELECT avg(balance) FROM accounts SAMPLE BY 1M

Timestamp search

Timestamp search can be performed with regular operators, e.g >, <= etc. However, QuestDB provides a native notation which is faster and less verbose.

Results in a given year

SELECT * FROM scores WHERE ts IN '2018';

Differences from standard SQL

SELECT * FROM is optional

In QuestDB, using SELECT * FROM is optional, so SELECT * FROM my_table; will return the same result as my_table;. While adding SELECT * FROM makes SQL look more complete, there are examples where omitting these keywords makes queries a lot easier to read.

Optional use of SELECT * FROM

my_table;

-- equivalent to:

SELECT * FROM my_table;

GROUP BY is optional

The GROUP BY clause is optional and can be omitted as the QuestDB optimizer derives group-by implementation from the SELECT clause. In standard SQL, users might write a query like the following:

SELECT a, b, c, d, sum(e) FROM tab GROUP BY a, b, c, d;

However, enumerating a subset of SELECT columns in the GROUP BY clause is redundant and therefore unnecessary. The same SQL in QuestDB SQL-dialect can be written as:

SELECT a, b, c, d, sum(e) FROM tab;

Implicit HAVING

Let's look at another more complex example using HAVING in standard SQL:

SELECT a, b, c, d, sum(e)

FROM tab

GROUP BY a, b, c, d

HAVING sum(e) > 100;

In QuestDB's dialect, featherweight sub-queries come to the rescue to create a smaller, more readable query, without unnecessary repetitive aggregations. HAVING functionality can be obtained implicitly as follows:

(SELECT a, b, c, d, sum(e) s FROM tab) WHERE s > 100;