Skip to main content

Tables And Schema

Table DDL runs inside an existing database. Create or select the database first, then create tables, alter columns, rename schema objects, or drop tables.

Create Tables​

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

Create a table only when it does not exist:

CREATE TABLE IF NOT EXISTS robots (
  id OID PRIMARY KEY NOT NULL,
  name STRING NOT NULL
);

Inline constraints can define primary keys and unique columns directly in the column list:

CREATE TABLE app_users (
  id STRING PRIMARY KEY NOT NULL,
  email STRING UNIQUE NOT NULL,
  display_name STRING NOT NULL
);

Declare a composite primary key after the column list:

CREATE TABLE readings (
  sensor_id STRING NOT NULL,
  ts INT64 NOT NULL,
  value FLOAT64 NOT NULL
) PRIMARY KEY (sensor_id ASC, ts DESC);

CamusDB also accepts inline index-style constraints inside CREATE TABLE:

CREATE TABLE robots (
  id OID NOT NULL,
  code STRING NOT NULL,
  name STRING,
  PRIMARY KEY (id),
  UNIQUE KEY code_uk (code),
  KEY name_idx (name)
);

Column Types​

SQL typeNotes
STRINGText values.
INT64Signed 64-bit integers.
FLOAT64Double-precision floating point values.
BOOLBoolean values.
OID24-character object id values.

BOOLEAN is accepted as an alias for BOOL, and OBJECT_ID is accepted as an alias for OID in type contexts.

Alter Tables​

Add or drop columns:

ALTER TABLE robots ADD COLUMN model STRING NULL;
ALTER TABLE robots DROP COLUMN model;

Add or drop a primary key:

ALTER TABLE robots ADD PRIMARY KEY (id);
ALTER TABLE robots DROP PRIMARY KEY;

Rename Tables​

ALTER TABLE robots RENAME TO machines;

After the rename, the old table name is no longer valid and the new table name resolves to the same table data. Row and index data survive the rename because the storage identity is not the table display name.

Renaming to an existing table name fails with TableAlreadyExists. Renaming a missing table fails with TableDoesntExist.

Rename Columns​

ALTER TABLE machines RENAME COLUMN name TO display_name;

Column rename is metadata-only for stored rows. Existing row values remain available under the new column name, and query results no longer include the old column name.

Renaming a missing column fails with UnknownColumn. Renaming to an existing column name fails with DuplicateColumn.

Drop Tables​

DROP TABLE robots;
DROP TABLE IF EXISTS robots;