# CREATE TABLE

[Read this page in HTML](https://www.scopedb.io/docs/reference/commands/statements/create-table)

Creates a table with named, typed columns and optional partitioning, clustering, deduplication, and retention rules.

## Syntax

```scopeql
CREATE TABLE [ IF NOT EXISTS ] <table_name> (
    [ <col_name> <col_type> [ AS (<expr>) ] [ COMMENT '<string_literal>' ] [, ...] ]
)
[ PARTITION BY <expr> [, ...] ]
[ CLUSTER BY <expr> [, ...] ]
[ DISTINCT ON <expr> [, ...] [ BY <order_by_expr> [, ...] ] ]
[ DATA RETENTION <days> DAY ]
[ COMMENT = '<string_literal>' ]
```

## Parameters

Use [ScopeQL data types](https://www.scopedb.io/docs/reference/data-types.md) for column types. `IF NOT EXISTS` leaves an existing table unchanged; it does not update its definition.

### `AS (<expr>)`

Defines a read-only [computed column](https://www.scopedb.io/docs/reference/computed-columns.md). The expression must use deterministic scalar operations over ordinary columns in the same table. Its result converts to `<col_type>`; evaluation or conversion errors produce NULL. The current expression also applies to historical rows.

### `PARTITION BY <expr> [, ...]`

Groups rows with the same partition key values. Choose expressions commonly used in filters, such as an hourly event-time bucket, so queries can skip unrelated data.

Partition expressions must be deterministic and non-constant. They can reference columns and functions, such as `trunc(event_time, unit => 'hour')`.

### `CLUSTER BY <expr> [, ...]`

Keeps rows with related cluster key values close together within a partition. Choose fields used in filters or range conditions, such as `service` or `event_time`.

Cluster expressions must be deterministic and non-constant. You can use clustering together with partitioning.

### `DISTINCT ON <expr> [, ...] [ BY ... ]`

Defines a deduplication key. `BY` specifies which row to retain for each key, using the same sort directions and NULL placement as [`ORDER BY`](https://www.scopedb.io/docs/reference/commands/stmt-query.md#order-by). For example, order by event time descending to retain the latest state of each order.

### `DATA RETENTION <days> DAY`

Sets automatic data retention. `<days>` must be a whole number from 1 through 1825. Omit this clause for no automatic retention policy. See the [data retention guide](https://www.scopedb.io/docs/guides/data-retention.md) for when data is removed and how to inspect the policy.

## Examples

### Store request events

Keep frequently queried fields in typed columns and additional attributes in an object. Partition by UTC hour and cluster by service:

```scopeql
CREATE TABLE request_events (
    event_time timestamp COMMENT 'Time the request completed',
    service string,
    latency_ms int,
    payload object
)
PARTITION BY trunc(event_time, unit => 'hour')
CLUSTER BY service
COMMENT = 'Completed application requests'
```

### Retain the latest order status

Keep the newest event for each order and apply a 30-day retention policy:

```scopeql
CREATE TABLE latest_order_events (
    order_id string,
    event_time timestamp,
    event_id string,
    status string
)
DISTINCT ON order_id BY event_time DESC NULLS LAST, event_id DESC
DATA RETENTION 30 DAY
```

## Related contents

* [Computed columns](https://www.scopedb.io/docs/reference/computed-columns.md)

* [Set data retention](https://www.scopedb.io/docs/guides/data-retention.md)