SQL overview

You talk to CamusDB in SQL, so the skills, queries, and tools you already use with a relational database carry over. CamusDB accepts common PostgreSQL expression forms, such as :: casts, so SQL that an ORM or a PostgreSQL tool generates usually needs few changes. Where CamusDB differs, these pages say so.

Run SQL from application code with the .NET, TypeScript, or Python driver. They bind values and handle the transport; the SQL reference does not require knowledge of JSON requests or gRPC messages. For interactive use, choose camus-cli or the Web Console.

The dialect covers what you expect from a relational database, plus a few additions:

• Schemas with CHECK constraints, foreign keys, sequences, identity columns, views, and materialized views.
• Queries with inner, outer, and cross joins, subqueries, derived tables, aggregates, window functions, and CASE expressions.
• Writes with INSERT ... SELECT and RETURNING.
• Serializable transactions, time-travel reads, vector search, row-level TTL, users and grants, and a large library of scalar functions.

This page covers the parts that belong to CamusDB alone and maps each statement to its page.

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

Keywords are not case-sensitive. CamusDB stores database, table, column, and index names in the case you use when you create or rename them, and later lookups ignore 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, and so do SHOW TABLES, SHOW COLUMNS, and SHOW CREATE TABLE. robots, ROBOTS, and Robots all refer to the same table.

Names are also unique regardless of case, so Robots and robots cannot be two tables in the same database, and one table cannot have both RobotName and robotname columns.

Backticks

Use backticks when an identifier matches 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, END, and OVER are reserved, so a column named end needs SELECT `end` FROM events.

Literals

Backticks quote identifiers only. String literals use single or double quotation marks, and the two forms are equivalent:

SELECT "literal text", 'literal text';

See Data Types for the literal format of every type, including temporal values, arrays, and object ids.

Comments

CamusDB accepts both line and block comments. A comment is valid anywhere a space is: before a statement, between 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;

Block comments do not nest: the first */ closes the comment. An unterminated comment is a parser error.

A comment marker inside a string literal is just text:

SELECT "not -- a comment" AS value;

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

Statement map

Schema

Task	Page
Create, rename, and drop databases	Databases
Branch a database	Database Branching
Restore a dropped database or table	Recover Dropped Objects
Tables, columns, and ALTER TABLE	Tables And Columns
Column types and literals	Data Types
CHECK and NOT NULL	Check Constraints
Parent-child integrity	Foreign Keys
CREATE INDEX, DROP INDEX, and covering indexes	Indexes
GIN indexes for JSONB	JSON Indexes
Sequences and identity columns	Sequences
Descriptions on schema objects	Schema Comments
Column compression and out-of-line storage	Large Values
A stored query	Views
A stored query result and REFRESH MATERIALIZED VIEW	Materialized Views

Read and write

Task	Page
INSERT, UPDATE, DELETE; LIMIT on updates and deletes	Insert, Update, Delete
RETURNING from inserts, updates, and deletes	Return Changed Rows
ON CONFLICT, UPSERT	Skip or Update Conflicting Rows
INSERT ... SELECT and CTAS	Copying Query Results
TRUNCATE, which empties a table	Emptying A Table
SELECT, filtering, grouping, sorting, and pagination	SELECT
FOR UPDATE, NOWAIT, SKIP LOCKED	Lock Rows Before Updating Them
Ranking, running totals, and comparisons between rows	Window Functions
Joins, subqueries, derived tables	Joins And Subqueries
UNION, INTERSECT, EXCEPT, including ALL	Combine Query Results
A historical snapshot	Time-Travel Reads
A SELECT with no table source	SELECT Without FROM
JSONB documents, operators, and updates	JSON Documents
PostgreSQL-style casts, arrays, and expression forms	PostgreSQL Expression Syntax
Scalar functions	Functions
Generate rows with generate_series	Set-Returning Functions
Custom C# functions, row triggers, and CALL procedures	C# Extensions
Vectors and nearest neighbor search	Vector Search
Row expiry	Row-Level TTL

Transactions, performance, and inspection

Task	Page
BEGIN, COMMIT, ROLLBACK, isolation, and locks	Transactions In SQL
Snapshot behavior and isolation guarantees	Transactions And Isolation
Retry a transaction after a conflict	Retries And Conflicts
Transaction size and lifetime limits	Transaction Limits
SET TRANSACTION PRIORITY	Transaction Priority
How CamusDB selects a plan	Query Planning
How to read a plan	EXPLAIN
The estimates behind a plan	SHOW STATISTICS
Refresh optimizer statistics with ANALYZE	Automatic Analyze
Caching repeated reads	Result Cache
Spill large query buffers to temporary files	Spill To Disk
Scans across the cluster	Distributed Queries
Learn routes for repeated SQL	SQL Routing Advice
The range placement of a table or index	SHOW RANGES
Active queries, connections, and CANCEL QUERY	Query Activity
Statements that crossed a slow-query threshold	Slow Query Log
Repeated query costs with SHOW STATEMENT STATS	Statement Statistics
Index reads and writes with SHOW INDEX USAGE	Index Usage
Storage and replication metrics with SHOW ENGINE STATS	Engine Stats
Effective configuration with SHOW VARIABLES	SHOW VARIABLES
Placeholders and prepared handles	Parameters And Prepared Statements
A deeply nested generated statement	Statement Nesting Limit
List databases, tables, columns, and indexes	Inspecting The Database
Users, passwords, GRANT, and REVOKE	Authentication And Authorization
SET/RESET CLUSTER SETTING and SHOW CLUSTER SETTINGS	Runtime Cluster Settings