Creates new table in the database.
The following sections describe the keywords and definitions illustrated in this diagram.
tableName - name is used to reference the table in SQL statements. Internally
the table name is used as a directory name on the file system. It can contain
both ASCII and Unicode characters.
tableNamemust be a unique name. An error is returned if a table already exists with the requested name.
Table names containing spaces or period
.character must be enclosed in double quotes, for example:CREATE TABLE "example out of.space" (a INT);INSERT INTO "example out of.space" values (1);
columnName - name used to reference a column of a table. As with table names,
the column name is used for file names internally. Although it does support both
ASCII and Unicode characters, character restrictions specific to the file system
columnNamemust be unique within each table and must not contain a period
The maximum number of columns in a table is 2,147,483,647
typeDef - column type definition with
additional options for
Optional keywords and parameters may follow the
symbol type which allow for
further optimization on the handling of this type. For more information on the
benefits of using this type, see the symbol overview.
CAPACITY- an optional keyword used when specifying a symbol type on table creation used to indicate how many distinct values this column is expected to have. When
distinctValueEstimateis not explicitly specified, a default value of
distinctValueEstimate- the value used to size data structures for symbols. These data structures will resize themselves when necessary to allow QuestDB to function correctly. Underestimating the symbol value count may result in drop of performance whereas over-estimating may result in higher disk space and memory consumption.
CACHE | NOCACHE- a flag to tell QuestDB how to cache a symbol.
CACHEmeans that QuestDB will use Java Heap based Map to resolve symbol values and keys. When a column has a large number of distinct symbol values (over 100,000, for example), the heap impact might be significant and may cause OutOfMemory errors, depending on the heap size. To avoid Java Heap impact,
NOCACHEleverages an off-heap structure which can deal with larger value counts but is slower.
The default option for
inlineIndexDef- when present, QuestDB will create and maintain an index for a
symbolcolumn. An index capacity definition may be provided (
indexCapacityDef) for storage configuration.
indexCapacityDef- storage options for the index using a
valueBlockSize- index storage parameter that specifies how many row IDs to store in a single storage block on disk. This value is optional and will default to the value of the
cairo.index.value.block.sizeconfiguration key. Fewer blocks used to store row IDs achieves better performance. At the same time over-sizing
valueBlockSizewill result in higher than necessary disk space usage.
Consider an example table with 200 unique stock symbols and 1,000,000,000 records over time. The index will have to store 1,000,000,000 / 200 row IDs for each symbol, i.e. 5,000,000 per symbol.
valueBlockSizeis 1,048,576 in this case, QuestDB will use 5 blocks to store the row IDs
valueBlockSizeis 1,024 in this case, the block count will be 4,883
castDef- casts type of cherry-picked column.
columnRefmust reference existing column in the
indexDef- instructs QuestDB to create an index for one of table's columns. This clause references column name to be indexed. The referenced column must be of type
timestamp - references a column in new table, which will be the designated
timestamp. Such column must be of type
timestamp. For more information on
designated timestamps, see the
designated timestamp reference.
The designated timestamp column cannot be changed after the table has been created.
partition by - the partitioning strategy for the
table. The default partitioning strategy of table is
NONE and tables can be
partitioned by one of the following:
The partitioning strategy cannot be changed after the table has been created.
The following examples demonstrate creating tables from basic statements, and introduce features such as partitioning and designated timestamps. For more information on the concepts introduced to below, see
- designated timestamp reference on electing a timestamp column
- partition documentation which describes how partitions work in QuestDB
- symbol reference for using the
This example will create a table without a designated timestamp and does not have a partitioning strategy applied.
The same table can be created and a designated timestamp may be specified. New records with timestamps which are out-of-order (O3) chronologically will be ordered at the point of ingestion. Configuring how the system handles ingestion of O3 records is done via O3 hysteresis configuration.
To partition this table by day, the following query may be used:
The following example shows how to create the same table with a designated timestamp, a partitioning strategy and providing parameters for handling the symbol type:
When SQL is
select * from tab or any arbitrary SQL result, the table data will
be copied with the corresponding structure.
where false condition.
Let's assume we imported a text file into the table
now we want to turn this data into time series through ordering trips by
pickup_time, assign dedicated timestamp and partition by month: