CREATE TABLE

Create append, keyed, and attribute tables, with the primary key, attribute, delta, index, and settings clauses.

Synopsis

CREATE TABLE [IF NOT EXISTS] <name> (
  <column> <type> [NOT NULL] [DEFAULT <expr>],
  ...
  [PRIMARY KEY (<columns>)]
  [ATTRIBUTE (<attr> <type>, ...)]
  [INDEX <name> <column> TYPE <type> [GRANULARITY n]]
)
[ORDER BY (<columns>)]
[SETTINGS <key> = <value>, ...];

Append table

CREATE TABLE trades (
  id      UInt64 NOT NULL,
  market  String NOT NULL,
  amount  UInt64 NOT NULL,
  PRIMARY KEY (id)
);

Keyed table

A keyed table upserts by primary key, so a read returns the latest value per key. Set it with mode = 'keyed':

CREATE TABLE balances (
  address  String NOT NULL,
  balance  UInt256,
  PRIMARY KEY (address)
) SETTINGS mode = 'keyed';

To pin live keys memory-resident for exact, spill-safe counts, put delta = {keep: 'latest'} on a storage policy and reference it (the keep field lives on the policy, not on CREATE TABLE):

CREATE STORAGE POLICY pin_p (
  serve_pool = 'default', durable = {pool: 'default'}, delta = {keep: 'latest'}
);

CREATE TABLE balances (
  address  String NOT NULL,
  balance  UInt256,
  PRIMARY KEY (address)
) SETTINGS mode = 'keyed', storage_policy = 'pin_p';

Attribute table

CREATE TABLE acct (
  id UInt64,
  ATTRIBUTE (bal UInt64, tier String)
);

Columns & defaults

CREATE TABLE accounts (
  id       UInt64 NOT NULL,
  balance  Float64,
  note     String DEFAULT 'x',
  PRIMARY KEY (id)
);

Inline indexes

CREATE TABLE t (
  id UInt64, amount UInt64, base_mint String,
  PRIMARY KEY (id),
  INDEX idx_amount amount TYPE minmax,
  INDEX bm_base_mint base_mint TYPE bitmap
);

See Indexes for the available types.

Settings

CREATE TABLE a (x Int64) SETTINGS storage_policy = 'memory_data';

Common settings keys (observed in engine tests):

Key	Purpose
`mode`	`append` (default) or `keyed` (upsert by primary key)
`storage_policy`	Built-ins `ephemeral_memory`, `memory_data`, `sync_disk`, `default`, or a custom `CREATE STORAGE POLICY`
`shard_by`, `shards`	Sharding column and shard count
`flush_rows`, `flush_bytes`	Delta flush triggers
`index_granularity`	Granule width for skip indexes
`ttl`, `state_ttl`	Retention — a predicate string, e.g. `ttl = 'ts < now() - INTERVAL 30 DAY'`
`durability`	Journaling / ack semantics (`none`, `async`, `sync`)
`layout`	Tail layout: `row` (keyed point access) or `columnar` (scans)
`refresh_mode`	System/materialized refresh behavior

TODO-verify: the exhaustive settings list and each key's accepted values are deployment- and release-specific. The keys above are all present in the engine's DDL tests.

ALTER — add/drop columns and attributes.
Storage model — what the settings actually control.

On this page