SQL.adoc

XTDB SQL Dialect

The following document assumes you are using XTDB’s Postgres Wire Protocol support.

Transactions

By default, XTDB is in READ ONLY mode. To commit a transaction, you will need to enter READ WRITE mode explicitly:

start transaction READ WRITE;
-- your DML here
commit;

INSERT

For now, insert behaves like upsert. You are permitted to run the following statements twice in a row and the second will not be rejected. By default, select will query across all of APPLICATION_TIME, so you will see two rows returned as well.

start transaction READ WRITE;
insert into users (id,name) values (1234,'wot');
commit;

The result of the insert statement might be surprising:

--
(0 rows)
INSERT 0 0

This is because XTDB is asynchronous. Your data has reached the Transaction Log but isn’t fully committed to the Object Store until commit.

UPDATE

start transaction READ WRITE;
update users set name='deo' where users.id=1234;
commit;

Be aware that the where clause requires fully-qualified columns, like select.

DELETE

start transaction READ WRITE;
delete from users where users.id=1234;
commit;

Queries

SELECT *

You might be surprised to see that select * from doesn’t behave the way you might expect from a database with schema-on-write:

=> select * from users;
ERROR:  unexpected server error during query execution

However, you can qualify your select * columns:

select * from users as users(id,name);

SELECT

Assuming the insert statement above, you will see:

=> select users.name from users;
 name
-------
 "wot"
(1 row)