To create a new table in the database, the
CREATE TABLE keywords followed by
column definitions are used.
To create a table by manually entering parameters and settings:
Checking table metadata can be done via the
functions which are described in the
meta functions documentation page.
To create a table by cloning the metadata of an existing table:
By default, created tables are Write-Ahead Log enabled
While we recommend WAL-enabled tables, it is still possible to create non-WAL-enabled tables.
global configuration setting allows you to
alter the default behaviour via
true: Creates a WAL table (default)
false: Creates a non-WAL table
And on an individual basis, you can also use
BYPASS WAL keyword indicates that the Write-Ahead Log should be bypassed.
This means that changes to the table will not be logged in the WAL, which can
improve performance in certain scenarios where the additional durability
guarantees provided by the WAL are not required. However, bypassing the WAL
means that if the database crashes or is not shut down cleanly, any recent
changes to the table might be lost. And that you cannot utilize WAL-requiring
features like deduplication.
BYPASS WAL during table creation, include it in the
statement after the column definitions and before any other table parameters
such as the designated timestamp or partitioning strategy, like so:
In the above statement,
BYPASS WAL creates the table without using the WAL.
The table will have a designated timestamp column
ts and will be partitioned
We recommend using WAL-tables. Keep in mind that using
BYPASS WAL is a
trade-off between write performance and data safety, and goes against the grain.
It should only be used when you're certain that the potential for data loss and
the absence of features is acceptable in your use case.
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.
Internally the table name is used as a directory name on the file system. It can contain both ASCII and Unicode characters. The table name must be unique and an error is returned if a table already exists with the requested name.
In addition, table names are case insensitive:
EXAMPLE are all treated the same. Table names containing spaces or period
. character must be enclosed in double quotes, for example:
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 still apply.
Tables may have up to 2,147,483,647 columns. Column names are also case
insensitive. For example:
EXAMPLE are all
treated the same. However, column names must be unique within each table and
must not contain a period
When specifying a column, a name and
type definition must be provided. The
type may have additional optional parameters applied.
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 is an optional keyword used when defining a symbol type on table
creation to indicate how many distinct values this column is expected to have.
distinctValueEstimate is not explicitly specified, a default value of
cairo.default.symbol.capacity is used.
distinctValueEstimate - the value used to size data structures for
The symbol capacity is not to be confused with index capacity described in column indexes below.
CACHE | NOCACHE is used to specify whether a symbol should be cached. The
default value is
CACHE unless otherwise specified.
castDef - casts the type of a specific column.
columnRef must reference
existing column in the
Create an IPv4-friendly table using the following pattern:
- creation of a table called
- columns for timestamp, source ipv4 address, destination ipv4 address
- a partition based on a day
An index capacity may be provided for the index by defining the index storage
See Index for more information about index capacity.
When SQL (
SELECT * FROM tab or any arbitrary SQL result, the
selected column names and their data type will be cloned to the new table.
The data type of a column can be changed:
The timestamp function allows for specifying which column (which must be of
timestamp type) should be a designated timestamp for the table. For more
information, see the designated timestamp
The designated timestamp column cannot be changed after the table has been created.
PARTITION BY allows for specifying the
partitioning strategy for the table. Tables created
via SQL are not partitioned by default (
NONE) and tables can be partitioned by
one of the following:
NONE: the default when partition is not defined.
The partitioning strategy cannot be changed after the table has been created.
When Deduplication is enabled, QuestDB only
inserts rows that do not match the existing data. When rows are inserted into a
table with the deduplication option configured, QuestDB searches for existing
rows to match using the specified
UPSERT KEYS. If a match is found, the
existing rows are replaced with the new row. If no match is found, the new rows
are inserted into the table.
Deduplication can only be enabled for Write-Ahead Log (WAL) tables.
It is possible to include multiple columns of different types in the
UPSERT KEYS list.
However, there are a few limitations to keep in mind:
- The designated timestamp column must be included in the list of columns
- Columns of STRING and BINARY types cannot be
After table creation the deduplication configuration can be changed at any time
- Enable deduplication and change
ALTER TABLE ENABLE
- Disable deduplication with using
ALTER TABLE DISABLE
The parameter influences how often commits of out-of-order data occur. It may be
set during table creation using the
maxUncommittedRows - defines the maximum number of uncommitted rows per-table
to keep in memory before triggering a commit for a specific table.
The purpose of specifying maximum uncommitted rows per table is to reduce the occurrences of resource-intensive commits when ingesting out-of-order data.
The global setting for the same parameter is
Checking the values per-table may be done using the
IN VOLUME clause is used to create a table in a different volume than the
standard. The table is created in the specified target volume, and a symbolic
link is created in the table's standard volume to point to it.
The use of the comma (
,) depends on the existence of the
WITHclause is present, a comma is mandatory before
IN VOLUME:CREATE TABLE my_table (i symbol, ts timestamp), index(i capacity 32) WITH maxUncommittedRows=7, IN VOLUME SECONDARY_VOLUME;
WITHclause is used, the comma must not be added for the
IN VOLUMEsegment:CREATE TABLE my_table (i symbol, ts timestamp) IN VOLUME SECONDARY_VOLUME;
The use of quotation marks (
') depends on the alias:
If the alias contains spaces, the quotation marks are required:CREATE TABLE my_table (i symbol, ts timestamp), index(i capacity 32) IN VOLUME 'SECONDARY VOLUME';
If the alias does not contain spaces, no quotation mark is necessary:CREATE TABLE my_table (i symbol, ts timestamp), index(i capacity 32) IN VOLUME SECONDARY_VOLUME;
The table behaves the same way as if it had been created in the standard
(default) volume, with the exception that
DROP TABLE removes the symbolic link from the
standard volume but the content pointed to is left intact in its volume. A table
using the same name in the same volume cannot be created again as a result, it
requires manual intervention to either remove or rename the table's directory in
The secondary table target volume is defined by
server.conf. The default setting contains
an empty list, which means the feature is not enabled.
To enable the feature, define as many volume pairs as you need, with syntax alias -> volume-root-path, and separate different pairs with a comma. For example:
Additional notes about defining the alias and volume root paths:
- Aliases are case-insensitive.
- Volume root paths must be valid and exist at bootstrap time and at the time when the table is created.
- Aliases and/or volume root paths can be single quoted, it is not required.
LIKE keyword clones the table schema of an existing table without copying
the data. Table settings and parameters such as designated timestamp, symbol
column indexes, and index capacity will be cloned, too.
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.
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: