To create a new table in the database, the
CREATE TABLE query followed by
column definitions can be used:
Hint: checking table metadata can be done via the
table_columns() functions which are described in the
meta functions documentation page.
The following sections describe the keywords and definitions illustrated in this diagram.
IF NOT EXISTS clause may be added directly after the
CREATE TABLE keywords to indicate that a new table should be created if one
with the desired table name does not already exist.
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.
Table parameters which influence how often commits of out-of-order data occur
may be set during table creation using the
WITH keyword. The following two
parameters may be applied:
maxUncommittedRows- equivalent to
commitLag- equivalent to
cairo.commit.lagexpects a value with a modifier to specify the unit of time for the value:
unit description us microseconds s seconds m minutes h hours d days
For more information on commit lag and the maximum uncommitted rows, see the guide for out-of-order commits.
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 out-of-order records is done via commit lag configuration.
The following example will create a table
my_table if one does not already
exist with the name
Let's assume we have out-of-order records arriving at a table
my_table. If we
know beforehand that the maximum lag of later records is likely to be 240
seconds, we can schedule sorting and commits of out-of-order data to occur
within this time boundary. The lag configuration can be combined with the
maximum uncommitted rows so that a commit will occur based on expected row
count, or the lag boundary is met:
For more information on out-of-order lag and uncommitted rows, see the documentation for out-of-order data commits.
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: