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>)]
[DELTA (keep = 'latest' | 'none')]
[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

DELTA (keep = 'latest') collapses to the latest value per primary key.

CREATE TABLE balances (
  address  String NOT NULL,
  balance  UInt256,
  PRIMARY KEY (address)
) DELTA (keep = 'latest');

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
`storage_policy`	`memory_data`, `disk_data`, `default`, `tiered`, `aged_tiered`, …
`shard_by`, `shards`	Sharding column and shard count
`flush_rows`, `flush_bytes`	Delta flush triggers
`index_granularity`	Granule width for skip indexes
`state_ttl`, `ttl`	Retention
`durability`	Journaling / ack semantics
`layout`	Tail layout (row vs columnar)
`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