# Tables

> The three table modes, APPEND, UPSERT and VERSIONED, and how keys, shards, partitions, order and storage profile shape a table.

A table is a typed schema plus a mode. The mode decides which writes the table
accepts and which state it keeps. Every other clause of `CREATE TABLE` shapes
where rows live and how they are stored; none of them changes the mode.

## The three modes

| Mode | Key | Accepted writes | State kept |
| --- | --- | --- | --- |
| `APPEND` | none: the engine assigns each row its identity | `INSERT` only | every row |
| `UPSERT` (the default) | exactly one `PRIMARY KEY` | `INSERT`, `UPSERT`, `REPLACE`, `UPDATE`, `DELETE` | the latest row per key |
| `VERSIONED` | exactly one `PRIMARY KEY` | the same as `UPSERT` | the current row per key and every accepted version |

`MODE` is optional; a table without it is an `UPSERT` table. A keyed mode has
one `PRIMARY KEY`, and an `APPEND` table carries no primary key.

### APPEND: a log of events

An append table keeps every row it accepts. Use it for events, readings and
other facts that are never corrected in place.

```sql
CREATE TABLE transfers (
  chain_id UINT16 NOT NULL,
  token STRING NOT NULL,
  sender STRING NOT NULL,
  recipient STRING NOT NULL,
  amount UINT256 NOT NULL,
  transferred_at DATETIME64(3, 'UTC') NOT NULL,
  _index UINT128 NOT NULL,
  INDEX transfers_ts (transferred_at) USING MINMAX SCOPE HISTORY GRANULE 8192
)
MODE APPEND
SHARD BY (token) SHARDS AUTO
PARTITION BY MONTH(transferred_at)
ORDER BY (chain_id, token, _index)
STORAGE PROFILE COLUMNAR;
```

An append table takes inserts; corrections are derived downstream, in a keyed
table maintained by a transformation.

### UPSERT: the latest row per key

An upsert table holds one current row per primary key. A later write to the same
key changes that row; the previous value is not retained.

```sql
CREATE TABLE token_metadata (
  chain_id UINT16 NOT NULL,
  token STRING NOT NULL,
  symbol STRING NULL ENCODING LOW_CARDINALITY,
  decimals UINT8 NULL,
  PRIMARY KEY (chain_id, token)
)
MODE UPSERT
SHARD BY (token) SHARDS 4;
```

### VERSIONED: current state plus history

A versioned table also keeps one current row per key, and it retains every
accepted version beside it. `VALID BY` and `REVISION BY` decide which version is
current when writes arrive out of order. See
[Versioned tables](/docs/concepts/versioned-tables).

```sql
CREATE TABLE balances (
  chain_id UINT16 NOT NULL,
  address STRING NOT NULL,
  token STRING NOT NULL,
  amount UINT256 NULL,
  updated_at DATETIME64(3, 'UTC') NULL,
  balance_index UINT128 NULL,
  balance_version UINT64 NULL,
  transfer_count UINT64 NULL,
  last_transfer_at DATETIME64(3, 'UTC') NULL,
  activity_index UINT128 NULL,
  PRIMARY KEY (chain_id, address, token),
  ATTRIBUTE GROUP holding (amount, updated_at, balance_index, balance_version)
    VALID BY (balance_index)
    REVISION BY (balance_version),
  ATTRIBUTE GROUP activity (transfer_count, last_transfer_at, activity_index)
    VALID BY (activity_index)
)
MODE VERSIONED
SHARD BY (address) SHARDS AUTO
ORDER BY (chain_id, address, token);
```

## Columns and nulls

A column is `NOT NULL` unless `NULL` is written after its type. A `DEFAULT`
takes one literal. A write that omits a column uses its default, or NULL when the
column is nullable. Primary-key columns and the columns named by `VALID BY` and
`REVISION BY` are non-null. The types are listed in
[Data types](/docs/sql/data-types).

## Write kinds

| Statement | Meaning |
| --- | --- |
| `INSERT` | creates a row; on a keyed table it reports an existing key |
| `UPSERT` | changes the named columns of an existing row, or creates the row |
| `REPLACE` | writes a complete row for the key |
| `UPDATE` | changes the named columns of existing rows |
| `DELETE` | removes rows by key or by condition |

Statement grammar is on
[INSERT](/docs/sql/insert) and [UPDATE and DELETE](/docs/sql/update-delete).

A keyed table can split its columns into
[attribute groups](/docs/concepts/attribute-groups): each group is written and
ordered on its own, so several writers can each own part of a row.

## Keys, shards, partitions and order

Four clauses are independent of each other:

| Clause | Decides | Default |
| --- | --- | --- |
| `PRIMARY KEY (…)` | row identity on a keyed table | none (required on UPSERT and VERSIONED) |
| `SHARD BY (…) SHARDS n` | which shard owns a row, and how many shards the table starts with | the primary key, and the node's automatic shard count |
| `PARTITION BY …` | how rows of a shard are bucketed for pruning: `NONE`, a UTC time bucket `HOUR(col)`, `DAY(col)` or `MONTH(col)`, or `HASH(col, buckets)` | `NONE` |
| `ORDER BY (…)` | the physical order of rows inside stored parts | the primary key |

Each shard is a tablet. Tablets are replicated across nodes and split and merge
as the table grows. See [ALTER TABLE](/docs/sql/alter) for schema changes.

## Storage profile

`STORAGE PROFILE ROW | COLUMNAR | HYBRID | DEFAULT` selects the physical
layout. `ROW` stores keyed rows for point access; `COLUMNAR` and `HYBRID` write
column blocks that favour scans. Append tables always store their parts as
column blocks. Per-column `ENCODING`, `COMPRESSION` and `CODEC` choose the
encoding of each column inside those blocks. See
[CREATE TABLE](/docs/sql/create-table) and [Storage](/docs/concepts/storage).

## Inspecting a table

`SHOW CREATE TABLE` returns the stored definition as a statement that can be run
again:

```sql
SHOW CREATE TABLE balances;
```

The other inspection statements are on
[Introspection](/docs/sql/introspection).

---

Source: https://nyxdb.io/docs/concepts/tables/