SQL extensions and compatibility

QuestDB SQL is based on ANSI SQL with extensions for time-series workloads and a small number of deliberate omissions where the standard form does not fit a column-oriented, time-ordered storage model. This page summarises what QuestDB adds, what it makes easier, what it does not support, and what works differently from PostgreSQL or other common dialects.

If you are looking for a specific keyword (DELETE, HAVING, OFFSET, DISTINCT ON, ON CONFLICT, ...) the Standard SQL features not supported section is the fastest way in.

Time-series clauses

QuestDB adds first-class SQL clauses for the patterns time-series workloads need most.

SAMPLE BY

SAMPLE BY aggregates data into evenly spaced time buckets aligned to the designated timestamp. It is shorter and faster than equivalent GROUP BY date_trunc(...) patterns and supports calendar alignment with time zones and DST.

Hourly average priceDemo this query

SELECT timestamp, symbol, avg(price)
FROM trades
WHERE timestamp IN '$today'
SAMPLE BY 1h;

SAMPLE BY supports FILL to handle missing buckets with strategies including NONE, NULL, PREV, LINEAR, and constants. ALIGN TO CALENDAR aligns buckets to civil time boundaries and supports TIME ZONE and WITH OFFSET.

LATEST ON

LATEST ON returns the most recent row per partition key, using the designated timestamp's physical sort order to avoid a full scan.

Latest trade per symbolDemo this query

SELECT * FROM trades
WHERE timestamp IN '$today'
LATEST ON timestamp PARTITION BY symbol;

This is the QuestDB equivalent of PostgreSQL's DISTINCT ON (...) and the window-function patterns commonly used in dialects that lack it.

TICK interval syntax

QuestDB extends WHERE with a concise interval syntax for the designated timestamp. It compiles to optimized interval scans and is both shorter and faster than dateadd() / now() arithmetic.

Last hourDemo this query

SELECT * FROM trades WHERE timestamp IN '$now - 1h..$now';

TodayDemo this query

SELECT * FROM trades WHERE timestamp IN '$today';

On QuestDB Enterprise, exchange calendars let TICK address venue schedules directly. XNYS is the ISO 10383 MIC code for the New York Stock Exchange; QuestDB knows the exchange's actual schedule, so holidays and early closes are excluded automatically.

NYSE trading hours for January 2025

SELECT * FROM trades WHERE timestamp IN '[2025-01]#XNYS';

Other supported calendars include XLON, XHKG, and more. See TICK operator for the full grammar.

Time-series joins

In addition to the standard joins (INNER, LEFT, RIGHT, FULL, CROSS, LATERAL), QuestDB provides joins designed for time-ordered data.

ASOF JOIN

ASOF JOIN attaches to each left row the most recent right row whose timestamp is less than or equal to the left timestamp. The textbook case is enriching trades with the prevailing quote.

Attach prevailing quote to each tradeDemo this query

SELECT t.timestamp, t.symbol, t.price, p.bid_price, p.ask_price
FROM fx_trades AS t
ASOF JOIN core_price AS p ON (symbol)
WHERE t.timestamp IN '$now-1h..$now';

A TOLERANCE clause caps how far back the join will look.

LT JOIN

LT JOIN is the strict-inequality variant of ASOF JOIN. The right row's timestamp must be strictly less than the left's. Use this when an equal timestamp would otherwise self-join.

SPLICE JOIN

SPLICE JOIN interleaves two time-ordered streams and, at each emitted row, exposes the latest value seen from each side so far. Used to maintain a live snapshot from two independent feeds.

WINDOW JOIN

WINDOW JOIN joins each left row to every right row inside a time range defined relative to the left row's timestamp. Used for "all market data in the next 100 ms after a trade" style queries.

HORIZON JOIN

HORIZON JOIN evaluates the right table at a list or range of time offsets from each left row. Used for post-trade markout, implementation-shortfall, and other time-series cohort analyses.

Markout at non-uniform time horizons after each tradeDemo this query

SELECT
    h.offset / 1_000_000_000 AS horizon_sec,
    t.symbol,
    avg((m.best_bid + m.best_ask) / 2 - t.price) AS avg_markout
FROM fx_trades AS t
HORIZON JOIN market_data AS m ON (symbol)
LIST (0, 5s, 30s, 1m) AS h
WHERE t.timestamp IN '$now-1h..$now'
ORDER BY t.symbol, horizon_sec;

For the full join inventory in one table, see Supported joins below.

Query syntax conveniences

These are differences from standard SQL that exist purely as ergonomics. They are optional shortcuts, not new semantics.

SELECT * FROM is optional

SELECT * FROM trades and trades return the same result. The shorter form is often easier to read inside subqueries.

Implicit SELECT *Demo this query

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

Implicit timestamp ordering and negative LIMIT

Queries against a table with a designated timestamp return rows in ascending timestamp order without an explicit ORDER BY. The data is already physically sorted on disk, so adding ORDER BY timestamp is redundant.

QuestDB also extends LIMIT with negative values, which take from the end of the result set. Combined with the implicit ordering, this gives "the latest N rows" with no sort:

Latest 10 tradesDemo this query

SELECT * FROM trades LIMIT -10;

LIMIT accepts a two-argument form with negative bounds for paginating from the end (LIMIT -m, -n takes the last m rows then drops the last n).

GROUP BY is optional

QuestDB derives the GROUP BY set from the non-aggregate columns in SELECT. Enumerating them again in a GROUP BY clause is redundant.

Implicit GROUP BYDemo this query

SELECT symbol, side, sum(price)
FROM trades
WHERE timestamp IN '$today';

The explicit GROUP BY form is still accepted.

Implicit HAVING

Standard SQL HAVING is not supported. Filter on aggregates by wrapping them in a subquery and applying a regular WHERE:

HAVING equivalent via subqueryDemo this query

(
    SELECT symbol, side, sum(price) AS total_price
    FROM trades WHERE timestamp IN '$today'
)
WHERE total_price > 10_000_000;

This pattern is consistently faster than HAVING in dialects that have it, because no extra aggregation pass is required.

DECLARE variables

DECLARE introduces query-scoped variables for both stand-alone queries and view definitions. Variables make repeated expressions (date ranges, symbol filters, thresholds) reusable without losing readability.

DECLARE used in a queryDemo this query

DECLARE
    @symbol := 'BTC-USDT',
    @window := '$now - 1d..$now'
SELECT timestamp, price FROM trades
WHERE symbol = @symbol AND timestamp IN @window;

Storage and table extensions

Designated timestamp

Every time-series table elects one column as its designated timestamp. Rows are stored physically sorted by this column, which is what makes SAMPLE BY, LATEST ON, ASOF JOIN, partition pruning, and interval scans efficient.

Partitioning and out-of-order data

Tables are partitioned by time (hour, day, week, month, year). Out-of-order writes are accepted and merged automatically. See Out-of-order data for per-ingestion-method behavior and tuning.

Materialized views

QuestDB materialized views are precomputed SAMPLE BY tables that refresh automatically as the base table receives data. Refresh strategies include IMMEDIATE, TIMER, PERIOD, and manual triggers. Views can be chained, so the output of one materialized view can serve as the base of another, and each view has its own TTL and partitioning.

Parameterized views

CREATE VIEW and COMPILE VIEW accept DECLARE OVERRIDABLE, which lets callers override view parameters at query time.

A view that accepts an overridable minimum price

CREATE VIEW expensive_trades AS (
    DECLARE OVERRIDABLE @min_price := 100
    SELECT * FROM trades WHERE price >= @min_price
);

-- Override at query time
DECLARE @min_price := 500 SELECT * FROM expensive_trades;

N-dimensional arrays

QuestDB supports n-dimensional DOUBLE arrays, typically used to store order-book levels (bids DOUBLE[][], asks DOUBLE[][]) and other structured numeric series. Arrays compose with the standard SQL operators and with UNNEST for row expansion.

Covering indices

In addition to the default bitmap index, SYMBOL columns support a posting index with INCLUDE columns. The included columns are stored inline with the index, so equality lookups read everything they need without touching the main column files.

Posting index with covering columns

CREATE TABLE trades (
    ts TIMESTAMP,
    symbol SYMBOL INDEX TYPE POSTING INCLUDE (price, amount),
    price DOUBLE,
    amount DOUBLE
) TIMESTAMP(ts) PARTITION BY DAY WAL;

PIVOT

QuestDB has native PIVOT following the DuckDB-style extended form. It is a superset of SQL Server's PIVOT:

Capability	SQL Server PIVOT	QuestDB PIVOT
Aggregates per call	One only	Multiple, comma-separated
FOR clauses	One	Multiple, producing a Cartesian product
IN list	Static literals only	Static list or subquery (parsed at compile time)
Value aliases	Not supported	'value' AS alias
Expressions inside aggregates	Column names only	Arbitrary expressions
GROUP BY	Implicit	Explicit, inside the PIVOT parens

PostgreSQL has no native PIVOT. The closest equivalent is the tablefunc.crosstab() extension or manual CASE WHEN / FILTER (WHERE ...) aggregates.

JSON and UNNEST

QuestDB supports JSON path expressions for reading inside VARCHAR columns that contain JSON. See JSON functions for the path-expression reference.

UNNEST expands both native arrays and JSON arrays into rows. Use it to flatten an ARRAY column for further filtering or aggregation:

UNNEST a literal arrayDemo this query

SELECT value FROM UNNEST(ARRAY[1.0, 2.0, 3.0]);

For JSON arrays stored as VARCHAR, UNNEST accepts a COLUMNS(...) clause that types each extracted field. The example below ingests a slice of the Coinbase trades API response and produces typed columns:

Expand a JSON array of trade objects

SELECT u.trade_id, u.price, u.size, u.side, u.time
FROM UNNEST(
    '[{"trade_id":994619709,"side":"sell","size":"0.00000100","price":"69839.36","time":"2026-04-06T10:32:55.517183Z"},
      {"trade_id":994619708,"side":"buy","size":"0.00000006","price":"69839.35","time":"2026-04-06T10:32:55.418434Z"}]'::VARCHAR
    COLUMNS(trade_id LONG, price DOUBLE, size DOUBLE, side VARCHAR, time TIMESTAMP)
) u;

Lifecycle, security, and operations

QuestDB exposes a few SQL-level features for operating tables at scale:

• TTL. Per-table retention that drops whole partitions older than the configured horizon.
• Storage policy (Enterprise). Moves old partitions to Parquet on object storage while keeping them queryable.
• RBAC (Enterprise). Users, groups, and service accounts with granular permissions over tables and operations.
• Backup / CHECKPOINT. Filesystem-level consistent snapshots for backup and restore.

Function library

QuestDB's function library is intentionally close to PostgreSQL where there is overlap. Most string, numeric, conditional, date, and aggregation functions take the names and signatures a PostgreSQL user would expect. See Aggregation functions, Date and time, and the rest of the function reference for details.

QuestDB also adds groups not typically found in general-purpose databases:

• Finance functions. L2 price reconstruction, weighted statistics, and other primitives for market data.
• Visualization functions. Sparkline-style summaries for the web console and text-based interfaces such as psql.
• Meta functions. tables(), table_partitions(), table_storage(), materialized_views(), query_activity(), and so on, for inspecting database state from SQL. Only a small subset of PostgreSQL's pg_catalog is implemented (enough for client tools to identify the server, for example pg_catalog.version()); use the QuestDB meta functions for actual introspection.
• Array functions for n-dimensional numeric arrays.

Learn by example

Two good places to see this material applied:

• demo.questdb.io. A live instance preloaded with FX, crypto, and order-book datasets. Most examples on this page run there unchanged.
• Cookbook. Recipes for finance, time-series patterns, Grafana integration, and ingestion.

To run SQL against a local QuestDB instance, use the built-in web console at http://localhost:9000, or connect with psql:

psql -h localhost -p 8812 -U admin -d qdb
# default password: quest

The web console works without any configuration. psql and other PostgreSQL clients connect over the PostgreSQL Wire Protocol on port 8812.

Standard SQL features not supported

When something a PostgreSQL or ANSI-SQL user expects does not work, the table below points at the QuestDB equivalent.

Standard SQL	QuestDB equivalent
DELETE FROM t WHERE ...	Row-level deletion is intentionally not supported. Use DROP PARTITION or TTL for retention, and deduplication for correction workflows. See Data retention.
UPDATE of the designated timestamp	Cannot be updated. Copy data through a temp table, modify, re-insert. See Updating data and Modifying data.
HAVING	Wrap aggregate in subquery and filter (see Implicit HAVING).
OFFSET n LIMIT m	LIMIT lo, hi (e.g. LIMIT 10, 30).
DISTINCT ON (col)	For the "latest row per group by timestamp" form (ORDER BY col, timestamp DESC), use LATEST ON timestamp PARTITION BY col. For any other ordering, wrap in a CTE with row_number() OVER (PARTITION BY col ORDER BY ...) and filter where the row number equals 1.
INSERT ... ON CONFLICT (...) DO UPDATE	DEDUP UPSERT KEYS(...) declared at table level.
Correlated subqueries in WHERE, EXISTS, scalar position	LATERAL JOIN for the row-correlated cases. Some scalar correlation can be expressed via ASOF or window functions.
generate_series() in SELECT projection	Use generate_series() in the FROM clause.
FETCH BACKWARD, scrollable cursors	Forward-only cursors. See pgwire caveats.
Stored procedures, triggers, PL/pgSQL	Not supported. Application code or scheduled jobs replace these patterns.
Foreign keys, CHECK constraints	Not supported. High-throughput ingestion paths (ILP, batch INSERT) avoid per-row constraint checks; integrity is typically enforced upstream in the ingestion pipeline.

Supported joins

For the full inventory in one place:

Join	Purpose
(INNER) JOIN	Standard inner join.
LEFT (OUTER) JOIN	Standard left outer join.
RIGHT (OUTER) JOIN	Standard right outer join.
FULL (OUTER) JOIN	Standard full outer join.
CROSS JOIN	Cartesian product.
LATERAL JOIN	Per-row correlated subquery join. The PG/SQL-standard tool for what other databases handle with correlated subqueries.
ASOF JOIN	Attach the latest preceding row from the right side.
LT JOIN	Strict-inequality variant of ASOF.
SPLICE JOIN	Interleave two streams, expose latest from each.
WINDOW JOIN	Right rows inside a time window of the left row.
HORIZON JOIN	Right table evaluated at one or more time offsets per left row.

Pgwire-specific caveats

When connecting via the PostgreSQL Wire Protocol, a few client-visible behaviors differ from a real PostgreSQL server:

• Forward-only cursors. FETCH BACKWARD and other backward operations are not supported. Configure drivers (psycopg2, asyncpg, JDBC, etc.) for forward-only iteration.
• Timestamp time zones. QuestDB stores all timestamps in UTC and emits them over the wire as TIMESTAMP WITHOUT TIMEZONE. Configure your client to interpret these as UTC; do not rely on driver-default local-time conversion. See Timestamp handling.
• Large result sets. Most drivers buffer entire result sets in memory by default, which can OOM the client on large queries. Use cursor-based fetching. See Large result sets.
• Protocol coverage. SSL, BLOB transfer, and remote file upload (COPY ... FROM stdin) are not supported over pgwire. See pgwire compatibility for the full list.

See also

• Query and SQL overview
• PostgreSQL Wire Protocol
• Designated timestamp
• Out-of-order data
• Materialized views
• Cookbook