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.

SELECT * FROM trades
WHERE timestamp IN today()
LATEST ON timestamp PARTITION BY symbol, side;

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.

SELECT * FROM trades WHERE timestamp IN '2025';

SAMPLE BY

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

SELECT timestamp, symbol, sum(price) FROM trades
WHERE timestamp in today()
SAMPLE BY 1h;

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.

trades;
-- equivalent to:
SELECT * FROM trades;

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 symbol, side, sum(price) FROM trades
WHERE timestamp IN today()
GROUP BY symbol, side;

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 symbol, side, sum(price) FROM trades
WHERE timestamp IN today();

Implicit HAVING

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

SELECT symbol, side, sum(price) FROM trades
WHERE timestamp IN today()
GROUP BY symbol, side
HAVING sum(price) > 1000;

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 symbol, side, sum(price) as total_price
  FROM trades WHERE timestamp IN today()
)
WHERE total_price > 10_000_000;