SQL overview

CamusDB speaks a compact dialect of SQL. The dialect covers the life of a database, a schema change, a write, a read, an index, and a transaction. Most of it looks familiar if you know SQL. This page covers the parts that belong to CamusDB alone.

CREATE DATABASE IF NOT EXISTS app;

CREATE TABLE robots (
  id OID PRIMARY KEY NOT NULL,
  name STRING NOT NULL,
  year INT64 DEFAULT (2024)
);

CREATE INDEX robots_year_idx ON robots (year DESC);

INSERT INTO robots (id, name, year)
VALUES (GEN_ID(), "R2-D2", 1977);

SELECT id, name, year
FROM robots
WHERE year >= 1970
ORDER BY year DESC;

Identifiers and case

A keyword is not case-sensitive. CamusDB stores the name of a database, a table, a column, and an index in the case of its creation. A later match on that name ignores the case:

CREATE TABLE Robots (
  Id OID PRIMARY KEY NOT NULL,
  RobotName STRING NOT NULL
);

INSERT INTO robots (id, robotname) VALUES (GEN_ID(), "R2-D2");
SELECT ROBOTNAME FROM ROBOTS;

The result still shows the original names Robots, Id, and RobotName. The three spellings robots, ROBOTS, and Robots all address the same table.

A name is also unique without regard to its case. Robots and robots therefore cannot exist as two tables in one database.

Backticks

Use a backtick when an identifier is the same as a reserved keyword, a type name, or a function name:

CREATE TABLE `order` (
  `select` STRING NOT NULL,
  `from` STRING
);

SELECT `select`, `from`
FROM `order`;

CASE and END are reserved. A column with the name end therefore needs SELECT `end` FROM events.

Literals

A backtick quotes an identifier only. A string literal takes a single quotation mark, or a double one. The two forms are equivalent:

SELECT "literal text", 'literal text';

See Data Types for the literal format of every type. That page includes a temporal value, an array, and an object id.

Comments

CamusDB accepts both forms of a comment. A comment is valid at any position where a space is valid: before a statement, between two clauses, or at the end of a line.

-- Line comments run to the end of the line.
SELECT id, name
FROM robots
WHERE year >= 1980; -- only newer robots

/*
  Block comments span multiple lines.
*/
SELECT id, name
FROM robots /* or sit inline */
WHERE active = true;

A block comment does not nest. The first */ closes the comment. A comment without an end is an error of the parser.

A comment marker inside a string literal is only text:

SELECT "not -- a comment" AS value;

One point is easy to miss. -- always starts a comment. SELECT 10 FROM t --5 therefore parses as SELECT 10 FROM t. Write 10 - -5, with a space, to subtract a negative number.

Map of the statements

Schema

Task	Page
Create and drop a database	Databases
Branch a database	Database Branching
Restore a dropped database or table	Recover Dropped Objects
Tables, columns, and ALTER	Tables And Columns
Column types and literals	Data Types
PostgreSQL-style expression forms	PostgreSQL Expression Syntax
CHECK and NOT NULL	Check Constraints
Indexes, and a covering index	Indexes
Sequences and identity columns	Sequences
A description on a schema object	Schema Comments
A stored query	Views
The stored result of a query	Materialized Views

Read and write

Task	Page
INSERT, UPDATE, DELETE	Insert, Update, Delete
INSERT ... SELECT and CTAS	Copying Query Results
TRUNCATE, which empties a table	Emptying A Table
SELECT, a filter, a group, an order	SELECT
A join, a subquery, a derived table	Joins And Subqueries
A historical snapshot	Time-Travel Reads
A SELECT with no table source	SELECT Without FROM
A scalar function	Functions
A vector, and a nearest neighbor search	Vector Search
The expiry of a row	Row-Level TTL

Transactions, performance, and inspection

Task	Page
BEGIN, COMMIT, the isolation, the locks	Transactions In SQL
How CamusDB selects a plan	Query Planning
How you read a plan	EXPLAIN
The estimates behind a plan	SHOW STATISTICS
A cache for a repeated read	Result Cache
A scan across the cluster	Distributed Queries
The range placement of a table or index	SHOW RANGES
Statements that crossed a slow-query threshold	Slow Query Log
A placeholder, and a prepared handle	Parameters And Prepared Statements
A deeply nested generated statement	Statement Nesting Limit
SHOW, DESCRIBE, ANALYZE	Inspecting The Database
A grant and a role	Authentication And Authorization
SET and RESET CLUSTER SETTING	Runtime Cluster Settings