5. CREATE TABLE#

5.1. Synopsis#

CREATE TABLE [ IF NOT EXISTS ] <table_name> (
  <column_name> <data_type> [ ( <column_size> ) ]
  [, ... ]
)
[ SHARD_SIZE [ = ] { <duration> | INFINITE } ]
[ WITH TAGS = '[' [ '<tag>' [, ... ] ] ']' ]
[ TTL [ = ] <duration> ]

<data_type> ::= BINARY | BLOB
              | FLOAT | DOUBLE
              | BIGINT | INT64
              | STRING
              | TIMESTAMP
              | SYMBOL [ ( <symbol_table_name> ) ]

Specify the options in the order that the synopsis shows.

5.2. Description#

CREATE TABLE creates a table with the specified column schema. To infer a schema from a query, use CREATE TABLE AS SELECT.

If the table exists, CREATE TABLE returns an error. If you specify IF NOT EXISTS, the statement succeeds and does not change the existing table.

QuasarDB supports time-series tables and general tables. The schema determines the table type:

  • Add one $timestamp TIMESTAMP column to create a time-series table. This column is the time index. It can occur at any position in the column list.

  • Do not add $timestamp to create a general table. A general table does not have a time index. This option is available in QuasarDB 3.14.3 and later.

The $timestamp column must have the TIMESTAMP type and must be unique. A table must also have at least one regular column. You cannot create a table that contains only $timestamp.

5.3. General tables#

A general table stores data that does not need a time index. You can use INSERT, SELECT, UPDATE, DELETE, ALTER TABLE, and DROP TABLE with general tables.

The following operations need a time index and do not support general tables:

  • IN RANGE

  • ASOF JOIN

  • COPY

  • IMPORT

  • TRIM TABLE

To use these operations, add $timestamp TIMESTAMP when you create the table. General tables always use an infinite shard size. If you specify SHARD_SIZE for a general table, QuasarDB does not use that value.

5.4. Parameters#

table_name

The name of the table to create. Use double quotation marks around an identifier that contains special characters.

column_name

The name of a column in the new table. Column names must be unique. Use double quotation marks around an identifier that contains special characters.

data_type

The column data type. BINARY is an alias for BLOB. FLOAT is an alias for DOUBLE. BIGINT is an alias for INT64.

column_size

An unsigned integer in parentheses after a data type. QuasarDB accepts this compatibility parameter but does not use it. It does not set a storage size or a value-length limit.

symbol_table_name

The symbol table for a SYMBOL column. If you omit the name, QuasarDB creates a symbol table and assigns an internal name.

SHARD_SIZE

The time span of one shard in a time-series table. Specify a duration or INFINITE. The default value is one day. The = character is optional.

WITH TAGS

A comma-separated list of tags to attach to the new table. Put each tag in single quotation marks. The = character is required.

TTL

The time-to-live value for the table data. Specify a duration. The = character is optional. For more information, see Time to live.

5.5. Examples#

Create a general table:

CREATE TABLE example (id INT64, description STRING)

Create a time-series table. The time index does not have to be the first column:

CREATE TABLE example (
  value DOUBLE,
  $timestamp TIMESTAMP,
  status SYMBOL(status_symbols)
)

Create a time-series table with a one-hour shard size and a seven-day TTL:

CREATE TABLE example (
  $timestamp TIMESTAMP,
  value DOUBLE
) SHARD_SIZE = 1hour TTL = 7day

Create a table only if it does not exist:

CREATE TABLE IF NOT EXISTS example (id INT64)

Create a table and attach three tags:

CREATE TABLE example (id INT64) WITH TAGS = ['tag1', 'tag2', 'tag3']

5.6. Time to live#

TTL removes expired table data during storage compaction. It does not remove the data at an exact time. Expired data can remain until a compaction processes it. For information about compaction, see Storage Optimization Techniques.

Use TTL when delayed removal is acceptable. Use DELETE FROM when you must remove a specified time range before the next compaction.

Set TTL when you create a table:

CREATE TABLE example (
  $timestamp TIMESTAMP,
  value INT64
) TTL = 7day

TTL cleanup normally occurs when new writes cause storage compaction. If the table does not receive new data, expired data can remain. To start a full cluster compaction, run cluster_compact full in the QuasarDB shell. A full compaction can put a high load on the cluster and can take a long time.