Skip to content
XTDB

SQL Transactions

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

Transaction operations

INSERT

Inserts documents into a table.

INSERT INTO <table name> ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) VALUES ( <value> , ) , ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) RECORDS <object> <param> , ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) <select query>

  • Documents must contain an _id column.

  • By default, the document will be inserted for valid-time between now and end-of-time. This can be overridden by including _valid_from and/or _valid_to columns in the document.

  • If the document already exists, 'insert' behaves like an upsert - it will overwrite the existing document for the valid-time range specified (or now → end-of-time if not provided).

UPDATE

Updates documents in a table, optionally for a period of valid-time.

UPDATE <table name> FOR PORTION OF VALID_TIME FROM <timestamp> TO <timestamp> FOR ALL VALID_TIME FOR VALID_TIME ALL SET <column> = <value> , WHERE <predicate>

  • If the valid-time range is not provided, the effective valid-time range of the update will be from now to the end of time. (SQL:2011 specifies that updates without this clause should be effective for all valid time; the now→end-of-time default is an XTDB deviation.)

  • The _id column cannot be updated - instead, users should delete this document and re-insert a new one.

  • The valid-time columns cannot be updated outside of the for-valid-time clause (i.e. not in the SET clause).

DELETE

Deletes documents from a table, optionally for a period of valid-time.

DELETE FROM <table name> FOR PORTION OF VALID_TIME FROM <timestamp> TO <timestamp> FOR ALL VALID_TIME FOR VALID_TIME ALL WHERE <predicate>

  • If the valid-time clause is not provided, the effective valid-time range of the delete will be from now to the end of time. (SQL:2011 specifies that deletes without this clause should be effective for all valid time; the now→end-of-time default is an XTDB deviation.)

ERASE

Irrevocably erases documents from a table, for all valid-time, for all system-time.

While XTDB is immutable, in some cases it is legally necessary to irretrievably delete data (e.g. for a GDPR request). This operation removes data such that even queries as of a previous system-time no longer return the erased data.

ERASE FROM <table name> WHERE <predicate>

ASSERT

Rolls back the transaction if the provided predicate is false.

This is used to enforce constraints on the data in a concurrent environment, such as ensuring that a document with a given ID does not already exist.

ASSERT <predicate>

  • We check to see whether the email address already exists in the database - if not, we can insert the new user.

    ASSERT NOT EXISTS (SELECT 1 FROM users WHERE email = 'james@example.com')

INSERT INTO users (_id, name, email) VALUES ('james', 'James', 'james@example.com')

  • Check the xt.txs table for the transaction result to see if the assertion failed.

    SELECT * FROM xt.txs;
     _id  | committed |            error              |          system_time
------+-----------+-------------------------------+-------------------------------
    0 | t         | null                          | "2024-06-25T16:45:16.492255Z"
    1 | t         | null                          | "2024-06-25T16:45:26.985539Z"
    2 | f         | ... "Precondition failed" ... | "2024-06-25T16:45:32.577224Z"
(3 rows)