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 symbol ID and sideDemo this query
SELECT * FROM trades
WHERE timestamp IN today()
LATEST ON timestamp PARTITION BY symbol, side;

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 yearDemo this query
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.

SAMPLE BY one month bucketsDemo this query
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.

Optional use of SELECT * FROMDemo this query
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:

Standard SQL GROUP BYDemo this query
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:

QuestDB Implicit GROUP BYDemo this query
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:

Standard SQL GROUP BY/HAVING
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:

QuestDB Implicit HAVING equivalentDemo this query
(
  SELECT symbol, side, sum(price) as total_price
  FROM trades WHERE timestamp IN today()
)
WHERE total_price > 10_000_000;