# CREATE TABLE

> The full CREATE TABLE grammar - columns, nullability, defaults, codecs, reducers, keys, groups, indexes, mode, ordering, shards, partitions and storage profile.

`CREATE TABLE` declares a table's columns, its mode and its physical layout in
one statement. For what the modes mean, read [Tables](/docs/concepts/tables)
first.

## Shape of the statement

The statement is `CREATE TABLE <name> ( <elements> ) <table clauses>`. The
elements inside the parentheses are comma-separated, in any order:

| Element | Form |
| --- | --- |
| column | `<column> <type> [column options]` |
| primary key | `PRIMARY KEY (<column>, …)` — at most once |
| attribute group | `ATTRIBUTE GROUP <group> (<column>, …) [VALID BY (…)] [REVISION BY (…)]` |
| inline index | `INDEX <index> (<column>, …) USING <structure>`, where the structure is `MINMAX`, `BLOOM` or `POSTINGS`; optionally `SCOPE CURRENT`, `SCOPE HISTORY` or `SCOPE BOTH`, and `GRANULE <rows>` |

The table clauses follow the closing parenthesis, in any order:

| Clause | Form | Default |
| --- | --- | --- |
| mode | `MODE APPEND`, `MODE UPSERT` or `MODE VERSIONED` | `UPSERT` |
| ordering | `VALID BY (<column>, …)`, `REVISION BY (<column>, …)` — each at most once | commit order |
| shards | `SHARD BY (<column>, …)`, optionally followed by `SHARDS <n>` or `SHARDS AUTO` | shard by the primary key; without `SHARDS`, the node's automatic shard count |
| partitions | `PARTITION BY NONE`, `HOUR(<column>)`, `DAY(<column>)`, `MONTH(<column>)` or `HASH(<column>, <buckets>)` | `NONE` |
| physical order | `ORDER BY (<column>, …)` | the primary key |
| storage profile | `STORAGE PROFILE ROW`, `COLUMNAR`, `HYBRID` or `DEFAULT` | `DEFAULT`, the node's profile |

A trailing `;` is optional. Names are unquoted ASCII identifiers and are stored
in lower case; see [Syntax](/docs/sql/syntax).

## Column options

After the type, a column takes each of these options at most once, in any
order:

| Option | Meaning |
| --- | --- |
| `NOT NULL` or `NULL` | nullability; a column is `NOT NULL` unless `NULL` is written |
| `DEFAULT <literal>` | the value used when a write omits the column; a literal only (number, string, `TRUE`, `FALSE` or `NULL`) |
| `ENCODING PLAIN` or `ENCODING LOW_CARDINALITY` | physical encoding; `LOW_CARDINALITY` is dictionary encoding |
| `COMPRESSION NONE`, `LZ4` or `ZSTD` | a one-stage compression shorthand |
| `CODEC(<stage>[(<int>, …)], …)` | an explicit codec chain, instead of `COMPRESSION` |
| `REDUCER REPLACE`, `SUM`, `MIN`, `MAX` or `RETAIN_FIRST` | how successive writes to the same key combine in this column |

Codec stage names: `NONE`, `Delta`, `DoubleDelta`, `GCD`, `T64`, `Gorilla`,
`FPC`, `LZ4`, `LZ4HC`, `ZSTD` A
chain has at most four stages and each stage at most four integer parameters.
`COMPRESSION` and `CODEC` are alternatives: a column takes one of them.

## Examples

A VERSIONED table of balances, with two attribute groups, each ordered by its own
coordinate:

```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);
```

An APPEND table of transfers, partitioned by month of a timestamp, with an explicit
physical order, a MINMAX index over history and a column-oriented layout:

```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 UPSERT table of token prices with codecs, a dictionary-encoded column and a
bloom index over current state:

```sql
CREATE TABLE token_metadata (
  chain_id UINT16 NOT NULL,
  token STRING NOT NULL,
  symbol STRING NULL ENCODING LOW_CARDINALITY,
  decimals UINT8 NULL REDUCER MAX,
  contract STRING NULL CODEC(ZSTD(3)),
  PRIMARY KEY (chain_id, token),
  INDEX token_contract (contract) USING BLOOM SCOPE CURRENT
)
MODE UPSERT
SHARD BY (token) SHARDS 4
STORAGE PROFILE ROW;
```

Attribute groups are covered on
[Attribute groups](/docs/concepts/attribute-groups); ordering on
[Versioned tables](/docs/concepts/versioned-tables).

## Reducers

A reducer decides what a write does to a column of an existing key. The
default is `REPLACE`: the new value overwrites the old one.

```sql
CREATE TABLE token_totals (
  chain_id UINT16 NOT NULL,
  token STRING NOT NULL,
  last_amount UINT256 NOT NULL REDUCER REPLACE,
  total_amount UINT256 NOT NULL REDUCER SUM,
  smallest UINT256 NOT NULL REDUCER MIN,
  largest UINT256 NOT NULL REDUCER MAX,
  first_amount UINT256 NOT NULL REDUCER RETAIN_FIRST,
  PRIMARY KEY (chain_id, token)
)
MODE VERSIONED;
```

A row written with 10 in every column, then with 7, 7, 7, 20 and 99, reads 7
(`REPLACE`), 17 (`SUM`), 7 (`MIN`), 20 (`MAX`) and 10 (`RETAIN_FIRST`). The raw
written values are retained beside the reduced row on a `VERSIONED` table.
`REDUCER NATIVE(…)` names a custom reducer.

## Rules checked at creation

- `UPSERT` and `VERSIONED` tables have exactly one `PRIMARY KEY`; an `APPEND`
  table carries no primary key, and its rows get an engine-generated identity.
- Column names are unique within the table.
- `SHARDS <n>` takes a positive count. `SHARDS AUTO`, or no `SHARDS` at all,
  takes the node's configured automatic shard count. Tablets split and merge as
  the data grows.
- On a table with attribute groups, `DEFAULT` values belong to grouped columns.
- Columns named by `VALID BY`, `REVISION BY` and `PRIMARY KEY` are non-null;
  `VALID BY` and `REVISION BY` columns are integers or timestamps.
- A table has at most 1,024 columns, 32 primary-key or shard columns, 256
  attribute groups and 256 inline indexes. Names are at most 128 bytes.
- The statement is at most 64 KiB.
- Table behaviour comes from `MODE`, the keys and `STORAGE PROFILE`; named
  policies (`… POLICY <name>`) attach placement, retention and compaction rules.

## SHOW CREATE TABLE and DROP TABLE

`SHOW CREATE TABLE <table>` returns the stored definition as a statement. That
statement can be run again to recreate the table:

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

`DROP TABLE [IF EXISTS] <table>` removes the table and its rows. Definitions
that read or write a table are dropped before the table. See
[DROP](/docs/sql/drop).

## Related pages

- [ALTER TABLE](/docs/sql/alter) for changes after creation.
- [Data types](/docs/sql/data-types) for the column types.
- [Indexes](/docs/concepts/indexes) for index structures and scopes.
- [INSERT](/docs/sql/insert) for writing rows.

---

Source: https://nyxdb.io/docs/sql/create-table/