To create a new table in the database, theCREATE TABLE keywords followed bycolumn definitions are used.

Syntax​

To create a table by manually entering parameters and settings:

To create a table by cloning the metadata of an existing table:

Examples​

The following examples demonstrate creating tables from basic statements, andintroduces feature such aspartitioning,designated timestamps and data deduplication. For more information on theconcepts introduced to below, see

• designated timestamp reference onelecting a timestamp column
• partition documentation which describes howpartitions work in QuestDB
• symbol reference for using thesymbol data type
• data deduplication reference on discardingduplicates.

This first iteration of our example creates a table with a designated timestampand also applies a partitioning strategy,BY DAY:

CREATE TABLE trades (
  timestamp TIMESTAMP,
  symbol SYMBOL,
  price DOUBLE,
  amount DOUBLE
) TIMESTAMP(timestamp)
PARTITION BY DAY;

Now we can add a time-to-live (TTL) period. Once an entire data partition ispast its TTL, it becomes eligible for automatic removal.

CREATE TABLE trades (
  timestamp TIMESTAMP,
  symbol SYMBOL,
  price DOUBLE,
  amount DOUBLE
) TIMESTAMP(timestamp)
PARTITION BY DAY
TTL 1 WEEK;

Next, we enable data deduplication. This will discard exact duplicates on thetimestamp and ticker columns:

CREATE TABLE trades (
  timestamp TIMESTAMP,
  symbol SYMBOL,
  price DOUBLE,
  amount DOUBLE
) TIMESTAMP(timestamp)
PARTITION BY DAY
TTL 1 WEEK
DEDUP UPSERT KEYS (timestamp, symbol);

Finally, we add additional parameters for our SYMBOL type:

CREATE TABLE trades (
  timestamp TIMESTAMP,
  symbol SYMBOL CAPACITY 256 NOCACHE,
  price DOUBLE,
  amount DOUBLE
) TIMESTAMP(timestamp)
PARTITION BY DAY
TTL 1 WEEK
DEDUP UPSERT KEYS (timestamp, symbol);

Write-Ahead Log (WAL) Settings​

By default, created tables areWrite-Ahead Log enabled. While we recommendWAL-enabled tables, it is still possible to create non-WAL-enabled tables.

CREATE TABLE'sglobal configuration setting allows you toalter the default behaviour viacairo.wal.enabled.default:

• true: Creates a WAL table (default)
• false: Creates a non-WAL table

And on an individual basis, you can also useBYPASS WAL.

Designated timestamp​

The timestamp function allows for specifying which column (which must be oftimestamp type) should be a designated timestamp for the table. For moreinformation, see thedesignated timestampreference.

The designated timestamp columncannot be changed after the table has beencreated.

Partitioning​

PARTITION BY allows for specifying thepartitioning strategy for the table. Tables createdvia SQL are not partitioned by default (NONE) and tables can be partitioned byone of the following:

• NONE: the default when partition is not defined.
• YEAR
• MONTH
• WEEK
• DAY
• HOUR

The partitioning strategycannot be changed after the table has beencreated.

Time To Live (TTL)​

To store and analyze only recent data, configure a time-to-live (TTL) period ona table using theTTL clause, placing it right afterPARTITION BY <unit>.You can't set TTL on a non-partitioned table.

Follow theTTL keyword with a number and a time unit, one of:

• HOURS
• DAYS
• WEEKS
• MONTHS
• YEARS

TTL units fall into two categories:

1. Fixed time periods:
   • HOURS
   • DAYS
   • WEEKS
2. Calendar-based periods:
   • MONTHS
   • YEARS

Fixed-time periods are always exact durations:1 WEEK is always 7 days.

Calendar-based periods may vary in length:1 MONTH from January 15th goes toFebruary 15th and could be between 28 and 31 days.

QuestDB accepts both singular and plural forms:

• HOUR orHOURS
• DAY orDAYS
• WEEK orWEEKS
• MONTH orMONTHS
• YEAR orYEARS

It also supports shorthand notation:3H for 3 hours,2M for 2 months.

Deduplication​

WhenDeduplication is enabled, QuestDB onlyinserts rows that do not match the existing data. When you insert a row into atable with deduplication enabled, QuestDB searches for existing rows withmatching values in all the columns specified withUPSERT KEYS. It replaces allsuch matching rows with the new row.

Deduplication only works onWrite-Ahead Log (WAL) tables.

You can include multiple columns of different types in theUPSERT KEYS list.

However, there are a few limitations to keep in mind:

• You must include the designated timestamp column
• You cannot use anARRAY column

You can change the deduplication configuration at any time usingALTER TABLE:

• Enable deduplication and changeUPSERT KEYS withALTER TABLE ENABLE
• Disable deduplication with usingALTER TABLE DISABLE

Examples​

CREATE TABLE trades (
  timestamp TIMESTAMP,
  symbol SYMBOL,
  price DOUBLE,
  amount DOUBLE
) TIMESTAMP(timestamp)
PARTITION BY DAY
DEDUP UPSERT KEYS (timestamp, symbol);

ALTER TABLE trades DEDUP ENABLE UPSERT KEYS (timestamp, symbol);

ALTER TABLE trades DEDUP DISABLE;

SELECT dedup FROM tables() WHERE table_name = '<the table name>';

SELECT `column`, upsertKey FROM table_columns('<the table name>');

IF NOT EXISTS​

An optionalIF NOT EXISTS clause may be added directly after theCREATE TABLE keywords to indicate that a new table should be created if onewith the desired table name does not already exist.

CREATE TABLE IF NOT EXISTS trades (
  timestamp TIMESTAMP,
  symbol SYMBOL,
  price DOUBLE,
  amount DOUBLE
) TIMESTAMP(timestamp)
PARTITION BY DAY;

Table name​

Internally the table name is used as a directory name on the file system. It cancontain both ASCII and Unicode characters. The table namemust be unique andan error is returned if a table already exists with the requested name.

In addition, table names are case insensitive:example,exAmPlE,EXAMplEandEXAMPLE are all treated the same. Table names containing spaces or period. character must be enclosed indouble quotes, for example:

CREATE TABLE "example out of.space" (a INT);
INSERT INTO "example out of.space" VALUES (1);

Column name​

As with table names, the column name is used for file names internally. Althoughit does support both ASCII and Unicode characters, character restrictionsspecific to the file system still apply.

Tables may have up to2,147,483,647 columns. Column names are also caseinsensitive. For example:example,exAmPlE,EXAMplE andEXAMPLE are alltreated the same. However, column namesmust be unique within each table andmust not contain a period. character.

Type definition​

When specifying a column, a name andtype definition must be provided. Thesymboltype may have additional optional parameters applied.

Symbols​

Optional keywords and parameters may follow thesymbol type which allow forfurther optimization on the handling of this type. For more information on thebenefits of using this type, see thesymbol overview.

Symbol capacity​

CAPACITY is an optional keyword used when defining a symbol type on tablecreation to indicate how many distinct values this column is expected to have.WhendistinctValueEstimate is not explicitly specified, a default value ofcairo.default.symbol.capacity is used.

distinctValueEstimate - the value used to size data structures forsymbols.

CREATE TABLE trades (
  timestamp TIMESTAMP,
  symbol SYMBOL CAPACITY 50,
  price DOUBLE,
  amount DOUBLE
) TIMESTAMP(timestamp)
PARTITION BY DAY;

Symbol caching​

CACHE | NOCACHE is used to specify whether a symbol should be cached. Thedefault value isCACHE unless otherwise specified.

CREATE TABLE trades (
  timestamp TIMESTAMP,
  symbol SYMBOL CAPACITY 50 NOCACHE,
  price DOUBLE,
  amount DOUBLE
) TIMESTAMP(timestamp);

Casting types​

castDef - casts the type of a specific column.columnRef must referenceexisting column in theselectSql

CREATE TABLE test AS (
  SELECT x FROM long_sequence(10)
), CAST (x AS DOUBLE);

Column indexes​

Index definitions (indexDef) are used to create anindex for a table column. The referenced table columnmust be of typesymbol.

CREATE TABLE trades (
  timestamp TIMESTAMP,
  symbol SYMBOL,
  price DOUBLE,
  amount DOUBLE
), INDEX(symbol) TIMESTAMP(timestamp);

See theIndex concept for moreinformation about indexes.

OWNED BY​

Enterprise only.

When a user creates a new table, they automatically get all table levelpermissions with theGRANT option for that table. However, if theOWNED BYclause is used, the permissions instead go to the user, group, or serviceaccount named in that clause.

TheOWNED BY clause cannot be omitted if the table is created by an externaluser, because permissions cannot be granted to them.

CREATE GROUP analysts;
CREATE TABLE trades (
  timestamp TIMESTAMP,
  symbol SYMBOL,
  price DOUBLE,
  amount DOUBLE
) TIMESTAMP(timestamp)
PARTITION BY DAY
OWNED BY analysts;

CREATE TABLE AS​

Creates a table, using the results from theSELECT statement to determine thecolumn names and data types.

CREATE TABLE new_trades AS (
  SELECT *
  FROM
    trades
) TIMESTAMP(timestamp);

We can use keywords such asIF NOT EXISTS,PARTITION BY..., as needed forthe new table. The data type of a column can be changed:

CREATE TABLE new_trades AS (
  SELECT *
  FROM
    trades
), CAST(price AS LONG) TIMESTAMP(timestamp);

Here we changed type ofprice toLONG.

ATOMIC​

Tables can be created atomically, which first loads all of the data and thencommits in a single transaction.

This requires the data to be available in memory all at once, so for largeinserts, this may have performance issues.

To force this behaviour, one can use theATOMIC keyword:

CREATE ATOMIC TABLE new_trades AS (
  SELECT *
  FROM
    trades
) TIMESTAMP(timestamp);

BATCH​

By default, tables will be created with data inserted in batches.

The size of the batches can be configured:

• globally, by setting thecairo.sql.create.table.model.batch.sizeconfiguration option inserver.conf.
• locally, by using theBATCH keyword in theCREATE TABLE statement.

CREATE BATCH 4096 TABLE new_trades AS (
  SELECT *
  FROM
    trades
) TIMESTAMP(timestamp);

One can also specify the out-of-order commit lag for these batched writes, usingthe o3MaxLag option:

CREATE BATCH 4096 o3MaxLag 1s TABLE new_trades AS (
  SELECT * FROM trades
) TIMESTAMP(timestamp);

Turning unordered data into ordered data​

As an additional example, let's assume we imported a text file into the tabletaxi_trips_unordered and now we want to turn this data into time seriesthrough ordering trips bypickup_time, assign dedicated timestamp andpartition by month:

CREATE TABLE taxi_trips AS (
  SELECT * FROM taxi_trips_unordered ORDER BY pickup_time
) TIMESTAMP(pickup_time)
PARTITION BY MONTH;

CREATE TABLE LIKE​

TheLIKE keyword clones the table schema of an existing table or materializedview without copying the data. Table settings and parameters such as designatedtimestamp and symbol column indexes will be cloned, too.

CREATE TABLE new_table (LIKE my_table);

WITH table parameter​

The parameter influences how often commits of out-of-order data occur. It may beset during table creation using theWITH keyword.

maxUncommittedRows - defines the maximum number of uncommitted rows per-tableto keep in memory before triggering a commit for a specific table.

The purpose of specifying maximum uncommitted rows per table is to reduce theoccurrences of resource-intensive commits when ingesting out-of-order data.

The global setting for the same parameter iscairo.max.uncommitted.rows.

CREATE TABLE trades (
  timestamp TIMESTAMP,
  symbol SYMBOL,
  price DOUBLE,
  amount DOUBLE
) TIMESTAMP(timestamp)
PARTITION BY DAY
WITH maxUncommittedRows=250000;

Checking the values per-table may be done using thetables() function:

SELECT id, table_name, maxUncommittedRows FROM tables();

Table target volume​

TheIN VOLUME clause is used to create a table in a different volume than thestandard. The table is created in the specified target volume, and a symboliclink is created in the table's standard volume to point to it.

The use of the comma (,) depends on the existence of theWITH clause:

• If theWITH clause is present, a comma is mandatory beforeIN VOLUME:

  CREATE TABLE trades (
    timestamp TIMESTAMP,
    symbol SYMBOL,
    price DOUBLE,
    amount DOUBLE
  ) TIMESTAMP(timestamp)
  PARTITION BY DAY
  WITH maxUncommittedRows=250000,
  IN VOLUME SECONDARY_VOLUME;
  

• If noWITH clause is used, the comma must not be added for theIN VOLUMEsegment:

  CREATE TABLE trades (
    timestamp TIMESTAMP,
    symbol SYMBOL,
    price DOUBLE,
    amount DOUBLE
  ) TIMESTAMP(timestamp)
  PARTITION BY DAY
  IN VOLUME SECONDARY_VOLUME;
  

The use of quotation marks (') depends on the volume alias:

• If the alias contains spaces, the quotation marks are required:

  CREATE TABLE trades (
    timestamp TIMESTAMP,
    symbol SYMBOL,
    price DOUBLE,
    amount DOUBLE
  ) TIMESTAMP(timestamp)
  PARTITION BY DAY
  IN VOLUME 'SECONDARY_VOLUME';
  

• If the alias does not contain spaces, no quotation mark is necessary.

Description​

The table behaves the same way as if it had been created in the standard(default) volume, with the exception thatDROP TABLE removes the symbolic link from thestandard volume but the content pointed to is left intact in its volume. A tableusing the same name in the same volume cannot be created again as a result, itrequires manual intervention to either remove or rename the table's directory inits volume.

Configuration​

The secondary table target volume is defined bycairo.volumes inserver.conf. The default setting containsan empty list, which means the feature is not enabled.

To enable the feature, define as many volume pairs as you need, with syntaxalias -> volume-root-path, and separate different pairs with a comma. Forexample:

cairo.volumes=SECONDARY_VOLUME -> /Users/quest/mounts/secondary, BIN -> /var/bin

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 timewhen the table is created.
• Aliases and/or volume root paths can be single quoted, it is not required.