SQL Queries

Changelog (last updated v2.2)

v2.2: a transaction’s basis is fixed at its first query

A transaction’s snapshot and clock time are fixed when its first query runs, as are any of its BEGIN or SET TRANSACTION options that bear on them.

Previously, BEGIN awaited the connection’s writes, captured the snapshot and fixed the clock itself. A transaction’s characteristics can now arrive after BEGIN, through SET TRANSACTION, so the basis waits until they have all landed - as a Postgres repeatable-read transaction takes its snapshot at its first statement. A transaction now sees a write that another connection makes between its BEGIN and its first query, and CURRENT_TIMESTAMP is the clock at that first query.

To keep a basis fixed at BEGIN, issue the transaction’s first query straight after it, or pin the basis explicitly with BEGIN READ ONLY WITH (SNAPSHOT_TOKEN = ..., CLOCK_TIME = ...).

For examples on how to run SQL queries in each client library, see the individual driver documentation.

Top-level queries

At the top-level, XTDB SQL queries augment the SQL standard in the following ways:

SELECT is optional - if not provided, it defaults to SELECT *
FROM is optional - if not provided, it defaults to a 0-column, 1-row table.

This enables queries of the form SELECT 1 + 2 - e.g. to quickly evaluate a data-less calculation.
SELECT may optionally be provided between GROUP BY and ORDER BY, for readability - at the place in the pipeline where it’s actually evaluated.
GROUP BY is inferred if not provided to be every column reference used outside of an aggregate function.

e.g. for SELECT a, SUM(b) FROM foo, XT will infer GROUP BY a

query

with clause

WITH clauses in XTDB are ‘optimization fences’ - XTDB will not attempt to optimize an outer query into a WITH clause, or across WITH clauses.
Supply MATERIALIZED to eagerly materialize a WITH clause, so that the results can be re-used multiple times in the same query.
WITH RECURSIVE is not yet supported in XTDB.

query term

NB:

SELECT is optional in XTDB - if not provided, it defaults to SELECT *
SELECT may be placed after GROUP BY in XTDB, so that the query clauses are written in the order that they’re executed in practice.
GROUP BY is optional in XTDB - if not provided, it defaults to all of the columns used outside of an aggregate function.
If you start your query with FROM, you may then include arbitrarily many sets of WHERE/GROUP BY/SELECT clauses, which will be evaluated in order.
Predicates can be comma-separated in XTDB, to aid with SQL generation - these are treated as conjuncts. There may be an arbitrary number of commas at the start, between any two predicate expressions, or at the end.
XTQL queries are sent within an SQL string literal - e.g. '(-> (from ...) ...)'. Given the possible presence of single quotes within the query, it is recommended to use dollar-delimited strings here: XTQL $$ <xtql query> $$

Due to implementation details in some drivers (e.g. PGJDBC), it is required to additionally specify the params in standard SQL (?) following your XTQL query, so that the driver knows how many arguments to allow.

For more details on XTQL queries, see the XTQL documentation.

select clause

star exclude

star rename

From clause, joins

from clause

relation

See query term for details on XTQL queries.
<table-function> covers both purpose-built table-valued functions (e.g. GENERATE_SERIES) and any scalar function call wrapped as a single-row table — see table functions.

temporal filter

Note: ONLY is only valid with VALID_TIME, not SYSTEM_TIME.

The valid-time/system-time filters for a given table take precedence as follows:

Any explicit specifications in the FROM clause.
Any options passed to the SETTING clause of the given query (see ‘Basis’).
Any options passed to the BEGIN clause of the current transaction (see ‘Basis’).
Then as follows:
- System time defaults to ‘as best known’ - the latest processed transaction on the queried node.
- Valid time defaults to ‘as of now’ - the clock time taken either from CLOCK_TIME (if overridden) or the actual clock time on the queried node.

`FOR VALID_TIME ONLY FROM X TO Y` (v2.2+)

FOR VALID_TIME ONLY FROM X TO Y selects rows whose valid-time period overlaps [X, Y) and clamps the projected _valid_from / _valid_to to that window. Rows whose stored period extends outside [X, Y) are returned with their bounds tightened to X / Y; rows fully inside the window are returned unchanged. The ONLY form is specific to VALID_TIME — there is no FOR SYSTEM_TIME ONLY.

Compare with the plain FROM/TO form, which matches the same rows but returns each row’s stored _valid_from / _valid_to:

-- selects rows overlapping [2003, 2007), returns stored bounds
SELECT _id, _valid_from, _valid_to FROM users
  FOR VALID_TIME FROM DATE '2003-01-01' TO DATE '2007-01-01';
-- => row stored 2000–2010 returns _valid_from=2000, _valid_to=2010

-- same rows, but bounds clamped into [2003, 2007)
SELECT _id, _valid_from, _valid_to FROM users
  FOR VALID_TIME ONLY FROM DATE '2003-01-01' TO DATE '2007-01-01';
-- => row stored 2000–2010 returns _valid_from=2003, _valid_to=2007

Use ONLY when you want each row’s projected validity to reflect the intersection of the row with the query window — e.g. computing how long a row was valid within a reporting period, or feeding a downstream consumer that expects bounds within the window. Use the plain form when you want to know the row’s full stored validity and only use [X, Y) to filter which rows come back.

ONLY also lets the engine skip pages that would only contribute to the parts of _valid_from / _valid_to that get clamped away, so it is generally faster than projecting a GREATEST / LEAST clamp on top of the plain form.

Expressions

value

param

record

literal

See Date/time types for more details on XTDB’s timestamp literals.

predicate

window

Note:

LEAD/LAG currently only support column references, not arbitrary expressions.
The default value parameter to LEAD/LAG is not yet supported.
IGNORE NULLS with LEAD/LAG is not yet supported.

Nested sub-queries

Nested sub-queries allow you to easily create tree-shaped results, using NEST_MANY and NEST_ONE:

For example, if you have a one-to-many relationship (e.g. customers → orders), you can write a query that, for each customer, returns an array of their orders as nested objects:

SELECT c._id AS customer_id, c.name,
       NEST_MANY(SELECT o._id AS order_id, o.value
                 FROM orders o
                 WHERE o.customer_id = c._id
                 ORDER BY o._id)
         AS orders
FROM customers c

⇒

[
  {
    "customerId": 0,
    "name": "bob",
    "orders": [ { "orderId": 0, "value": 26.20 }, { "orderId": 1, "value": 8.99 } ]
  },
  {
    "customerId": 1,
    "name": "alice",
    "orders": [ { "orderId": 2, "value": 12.34 } ]
  }
]

In the other direction (many-to-one) - for each order, additionally return details about the customer - use NEST_ONE to get a single nested object:

SELECT o._id AS order_id, o.value,
       NEST_ONE(SELECT c.name FROM customers c
                WHERE c._id = o.customer_id)
         AS customer
FROM orders o
ORDER BY o._id

⇒

[
  {
    "orderId": 0,
    "value": 26.20,
    "customer": { "name": "bob" }
  },
  {
    "order-id": 1,
    "value": 8.99,
    "customer": { "name": "bob" }
  },
  {
    "order-id": 2,
    "value": 12.34,
    "customer": { "name": "alice" }
  }
]

Basis

Queries in XTDB run against a ‘basis’, which consists of:

a ‘snapshot’ - an upper bound on the transactions that are visible to the query.
a ‘clock time’ - used for any function calls that reference the current time (e.g. CURRENT_TIMESTAMP)

These can be set either on a per-query basis, using SETTING, or for a transaction, using BEGIN or SET TRANSACTION. A transaction’s basis is fixed when its first query runs, and every query in the transaction shares it.

SETTING

Setting the default valid-time/system-time applies to any FROM clause that doesn’t have any valid-time/system-time specification explicitly set.
Setting the SNAPSHOT_TOKEN enforces an upper-bound on the transactions visible to the query - i.e. no matter what the per-table system-time clauses specify, they will not see anything newer than this snapshot-token. If not provided, this defaults to the latest-completed transaction on the queried node.
Setting the CLOCK_TIME defines a fixed value for any functions that depend on the current time - e.g. CURRENT_TIMESTAMP. It also defines the default valid-time selection for any tables in FROM clauses that don’t otherwise have a valid-time specification. If not provided, it defaults to the clock-time fixed at the transaction’s first query.

BEGIN / COMMIT / ROLLBACK

A transaction may be either READ ONLY or READ WRITE. If not specified, it will be inferred from the first statement in the transaction.

Transactions must not mix query statements and DML statements.
BEGIN’s transaction modes are those of SET TRANSACTION: BEGIN READ ONLY, CLOCK_TIME = ... opens a transaction and applies SET TRANSACTION READ ONLY, CLOCK_TIME = ... to it.
BEGIN READ ONLY WITH (...) takes the same options inside WITH (...), TIMEZONE included.
For read-write transactions, see the transaction reference.
Committing/rolling back a read-only transaction has no effect in XTDB, because readers never block writers nor each other.
BEGIN, START TRANSACTION, COMMIT and ROLLBACK are SQL statements over the Postgres wire protocol only. ADBC and Flight SQL clients open and end transactions through their own transaction API, and reject these statements as SQL; they set a transaction’s options with SET TRANSACTION.

SET TRANSACTION (v2.2+)

SET TRANSACTION sets the open transaction’s access mode and options, as a comma-separated list of transaction modes.
- The access mode, READ ONLY or READ WRITE, may appear anywhere in the list.
- An option belonging to one access mode requires that access mode in the same statement - SET TRANSACTION CLOCK_TIME = ... on its own is rejected.
- TIMEZONE and ISOLATION LEVEL need no access mode.
A later SET TRANSACTION sets only the modes it names, and keeps the rest:
- <option> = DEFAULT clears an option set earlier.
- Changing the access mode clears the previous access mode’s options. The time zone is kept.
It is accepted until the transaction’s first query or first write - afterwards, it is rejected.
Outside a transaction, it is rejected under auto-commit, and opens a transaction otherwise.
ISOLATION LEVEL ... is accepted, and has no effect: XTDB transactions are always serializable. SET TRANSACTION ISOLATION LEVEL ... on its own is accepted anywhere.
TIMEZONE sets the time zone for the duration of the transaction, affecting any time zone-aware date/time literals and functions. If not provided, it defaults to the time-zone of the connection.
For read-only transactions:
- SNAPSHOT_TOKEN and CLOCK_TIME behave the same as in SETTING.
- AWAIT_TOKEN may be provided to wait for a specific transaction to be visible on the queried node before the transaction’s first query runs. If not provided, it defaults to waiting for the latest-submitted transaction on the current connection.
For read-write transactions, see the transaction reference.