Create Tables

Copy-paste examples of how to create tables with SQL

For the full table creation syntax, see CREATE TABLE.


Column Types

For the full set of column types, see Column Types.

Numeric 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE example_cts.column_types_numeric
(
    b   BOOLEAN,          -- Native "boolean" type
    ti  TINYINT,          -- Native "int8" type
    si  SMALLINT,         -- Native "int16" type
    i   INT,              -- Native "integer" type
    bi  BIGINT,           -- Native "long" type
    ub  UNSIGNED BIGINT,  -- Native "ulong" type
    r   REAL,             -- Native "float" type
    d   DOUBLE,           -- Native "double" type
    dc  DECIMAL(18, 4)    -- Native "decimal" type
)
Text (Simple) 
1
2
3
4
5
6
7

CREATE TABLE example_cts.column_types_text_simple
(
    c_min  VARCHAR(1),    -- Native "char1" type
    c_max  VARCHAR(256),  -- Native "char256" type
    s      VARCHAR,       -- Native "string" type
    sn     VARCHAR(512)   -- Native "string" type
)
Text (Complex) 
1
2
3
4
5
6

CREATE TABLE example_cts.column_types_text_complex
(
    ip  IPV4,  -- Native "ipv4" column property on "string" type
    ui  UUID,  -- Native "uuid" type
    j   JSON   -- Native "json" type
)
Date/Time 
1
2
3
4
5
6
7

CREATE TABLE example_cts.column_types_date_time
(
    td  DATE,      -- Native "date" type
    dt  DATETIME,  -- Native "datetime" type
    tt  TIME,      -- Native "time" type
    ts  TIMESTAMP  -- Native "timestamp" type
)
Binary 
1
2
3
4

CREATE TABLE example_cts.column_types_binary
(
    b  BLOB  -- Native "bytes" type
)
Geospatial 
1
2
3
4
5

CREATE TABLE example_cts.column_types_geospatial
(
    geo  GEOMETRY,  -- Native "wkt" subtype of "string" type
    bw   BLOB(WKT)  -- Native "wkt" subtype of "bytes" type
)
Array 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE example_cts.column_types_array
(
    ab    BOOLEAN[3],  -- Native "array(boolean, 3)" subtype of "string" type
    ai    INT[3],      -- Native "array(int, 3)" subtype of "string" type
    abi   BIGINT[3],   -- Native "array(long, 3)" subtype of "string" type
    avec  VECTOR(3),   -- Native "vector(3)" type
    ar    REAL[3],     -- Native "array(float, 3)" subtype of "string" type
    ad    DOUBLE[3],   -- Native "array(double, 3)" subtype of "string" type
    avar  VARCHAR[3]   -- Native "array(string, 3)" subtype of "string" type
)

Column Features

Any column type can have the nullability modified or have a comment applied. Column properties like dictionary encoding, text searchability, and the initial value modifiers are shown with all applicable column types.

Nullability 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

CREATE TABLE example_cts.column_features_nullability
(
    b      BOOLEAN       NOT NULL,
    i      INT               NULL,
    r      REAL          NOT NULL,
    d      DOUBLE            NULL,
    v      VARCHAR       NOT NULL,
    c_min  VARCHAR(1)        NULL,
    c_max  VARCHAR(256)  NOT NULL,
    ip     IPV4              NULL,
    ui     UUID          NOT NULL,
    td     DATE              NULL,
    bl     BLOB          NOT NULL,
    geo    GEOMETRY          NULL,
    bw     BLOB(WKT)     NOT NULL,
    ai     INT[3]            NULL,
    avec   VECTOR(3)     NOT NULL
)
Dictionary Encoding 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE TABLE example_cts.column_features_dictionary_encoding
(
    b      BOOLEAN(DICT),
    ti     TINYINT(DICT),
    si     SMALLINT(DICT),
    i      INT(DICT),
    bi     BIGINT(DICT),
    r      REAL(DICT),
    d      DOUBLE(DICT),
    dc     DECIMAL(10,4,DICT),
    v      VARCHAR(DICT),
    c_min  VARCHAR(1,DICT),
    c_max  VARCHAR(256,DICT),
    ub     UNSIGNED BIGINT(DICT),
    ip     IPV4(DICT),
    ui     UUID(DICT),
    td     DATE(DICT),
    dt     DATETIME(DICT),
    tt     TIME(DICT),
    ts     TIMESTAMP(DICT),
    bl     BLOB(DICT)
)
Text Search 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE example_cts.column_features_text_search
(
    v      VARCHAR(TEXT_SEARCH),
    c_min  VARCHAR(1,TEXT_SEARCH),
    c_max  VARCHAR(256,TEXT_SEARCH),
    td     DATE(TEXT_SEARCH),
    tt     TIME(TEXT_SEARCH),
    dt     DATETIME(TEXT_SEARCH),
    ip     IPV4(TEXT_SEARCH),
    ui     UUID(TEXT_SEARCH)
)
Initial Values 
1
2
3
4
5
6
7
8

CREATE TABLE example_cts.column_features_init_values
(
    ui  UUID(INIT_WITH_UUID),
    td  DATE(INIT_WITH_NOW),
    dt  DATETIME(INIT_WITH_NOW),
    tt  TIME(INIT_WITH_NOW),
    ts  TIMESTAMP(INIT_WITH_NOW)
)
Update Values 
1
2
3
4
5
6
7

CREATE TABLE example_cts.column_features_update_values
(
    td  DATE(UPDATE_WITH_NOW),
    dt  DATETIME(UPDATE_WITH_NOW),
    tt  TIME(UPDATE_WITH_NOW),
    ts  TIMESTAMP(UPDATE_WITH_NOW)
)
Comment 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

CREATE TABLE example_cts.column_features_comments
(
    b      BOOLEAN      COMMENT 'Native "boolean" type',
    i      INT          COMMENT 'Native "int" type',
    r      REAL         COMMENT 'Native "float" type',
    d      DOUBLE       COMMENT 'Native "double" type',
    v      VARCHAR      COMMENT 'Native "string" type',
    c_min  VARCHAR(1)   COMMENT 'Native "char1" type',
    c_max  VARCHAR(256) COMMENT 'Native "char256" type',
    ip     IPV4         COMMENT 'Native "ipv4" column property on "string" type',
    ui     UUID         COMMENT 'Native "uuid" type',
    td     DATE         COMMENT 'Native "date" type',
    bl     BLOB         COMMENT 'Native "bytes" type',
    geo    GEOMETRY     COMMENT 'Native "wkt" subtype of "string" type',
    bw     BLOB(WKT)    COMMENT 'Native "wkt" subtype of "bytes" type',
    ai     INT[3]       COMMENT 'Native "array(int, 3)" subtype of "string" type',
    avec   VECTOR(3)    COMMENT 'Native "vector(3)" type'
)

Keys

Primary keys, shard keys, & foreign keys, are applicable to most column types. See the respective sections for details.

Primary 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE example_cts.pk
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE,
    PRIMARY KEY (id)
)
Soft Primary 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE example_cts.pk_soft
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE,
    SOFT PRIMARY KEY (id)
)
On-Disk Primary 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE example_cts.pk_disk
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE,
    PRIMARY KEY (id)
)
USING TABLE PROPERTIES (PRIMARY_KEY_TYPE = 'disk')
Shard 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE example_cts.sk
(
    id         TINYINT,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE,
    SHARD KEY (dept_id)
)
Primary/Shard 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE example_cts.pk_sk
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT NOT NULL,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE,
    PRIMARY KEY (id, dept_id),
    SHARD KEY (dept_id)
)
Foreign 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE example_cts.fk
(
    id         TINYINT,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE,
    SHARD KEY (dept_id),
    FOREIGN KEY (dept_id) REFERENCES example_cts.fk_pk(id)
)
Composite Primary 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE example_cts.pk_comp
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT NOT NULL,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE,
    PRIMARY KEY (id, dept_id)
)
Composite Shard 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE example_cts.sk_comp
(
    id         TINYINT,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE,
    SHARD KEY (id, dept_id)
)
Composite Foreign 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE example_cts.fk_comp
(
    id         TINYINT,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE,
    SHARD KEY (id, dept_id),
    FOREIGN KEY (id, dept_id) REFERENCES example_cts.fk_pk_comp(id, dept_id)
)

Indexes

Column & low-cardinality indexes have wide column type applicability; while chunk skip, geospatial, CAGRA, & HNSW indexes are shown with all applicable column types. See the respective sections for details.

Column (Attribute) 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE example_cts.indexes_column
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE
)
INDEX(id)
INDEX(dept_id)
Low-Cardinality 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE example_cts.indexes_low_cardinality
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT NOT NULL,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE
)
LOW CARDINALITY INDEX(dept_id)
LOW CARDINALITY INDEX(manager_id)
Chunk Skip 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

CREATE TABLE example_cts.indexes_chunk_skip
(
    i      INTEGER,
    bi     BIGINT,
    ub     UNSIGNED BIGINT,
    c_min  VARCHAR(1),
    c_max  VARCHAR(256),
    td     DATE,
    dt     DATETIME,
    tt     TIME,
    ts     TIMESTAMP
)
CHUNK SKIP INDEX(i)
CHUNK SKIP INDEX(bi)
CHUNK SKIP INDEX(ub)
CHUNK SKIP INDEX(c_min)
CHUNK SKIP INDEX(c_max)
CHUNK SKIP INDEX(td)
CHUNK SKIP INDEX(dt)
CHUNK SKIP INDEX(tt)
CHUNK SKIP INDEX(ts)
Geospatial 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE example_cts.indexes_geospatial
(
    low_res_x   REAL,
    low_res_y   REAL,
    high_res_x  DOUBLE,
    high_res_y  DOUBLE,
    geo         GEOMETRY,
    bw          BLOB(WKT)
)
GEOSPATIAL INDEX(low_res_x, low_res_y)
GEOSPATIAL INDEX(high_res_x, high_res_y)
GEOSPATIAL INDEX(geo)
GEOSPATIAL INDEX(bw)
CAGRA 
1
2
3
4
5
6
7
8

CREATE TABLE example_cts.indexes_cagra
(
    v_def  VECTOR(100),
    v_opt  VECTOR(1000)
)
CAGRA INDEX(v_def)
CAGRA INDEX(v_opt)
    WITH OPTIONS (INDEX_OPTIONS = 'itopk_size: 128, graph_build_algo: ivf_pq')
HNSW 
1
2
3
4
5
6
7
8

CREATE TABLE example_cts.indexes_hnsw
(
    v_def  VECTOR(100),
    v_opt  VECTOR(1000)
)
HNSW INDEX(v_def)
HNSW INDEX(v_opt)
    WITH OPTIONS (INDEX_OPTIONS = 'm: 32, ef: 128, ef_construction: 16')

Collision Options (Table Exists)

No Error 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE IF NOT EXISTS example_cts.collision_options_no_error
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE
)
Replace 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE OR REPLACE TABLE example_cts.collision_options_replace
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE
)

Distribution

See sections on replicated, sharded, & randomly sharded tables for details.

Replicated 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE REPLICATED TABLE example_cts.distribution_replicated
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE
)
Sharded (Explicit via Shard Key) 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE example_cts.distribution_sharded_explicit
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE,
    SHARD KEY(id)
)
Sharded (Implicit via Primary Key) 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE example_cts.distribution_sharded_implicit
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE,
    PRIMARY KEY(id)
)
Randomly Sharded 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE example_cts.distribution_random
(
    id         TINYINT,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE
)

Properties

Memory Only (Temporary) 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TEMP TABLE example_cts.properties_memory_only
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE
)
Chunk Size 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE example_cts.properties_chunk_size
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE
)
USING TABLE PROPERTIES(CHUNK SIZE = 1000000)
TTL 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE example_cts.properties_ttl
(
    id         TINYINT NOT NULL,
    dept_id    TINYINT,
    manager_id TINYINT,
    first_name VARCHAR(32),
    last_name  VARCHAR(64),
    salary     DECIMAL(18, 4),
    hire_date  DATE
)
USING TABLE PROPERTIES(TTL = 15)

Partitions

See section on partitioning for details.

Range 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

CREATE TABLE example_cts.partition_range_by_year
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP NOT NULL
)
PARTITION BY RANGE (YEAR(purchase_ts))
PARTITIONS
(
    order_2014_2016 MIN(2014) MAX(2017),
    order_2017                MAX(2018),
    order_2018                MAX(2019),
    order_2019                MAX(2020)
)
Interval 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE example_cts.partition_interval_by_day
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP
)
PARTITION BY INTERVAL (purchase_ts)
PARTITIONS
(
    STARTING AT ('2014-01-01') INTERVAL (INTERVAL '1' DAY)
)
List (Manual) 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

CREATE TABLE example_cts.partition_list_manual_by_year
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP NOT NULL
)
PARTITION BY LIST (YEAR(purchase_ts))
PARTITIONS
(
    order_2014_2016 VALUES (2014, 2015, 2016),
    order_2017      VALUES (2017),
    order_2018      VALUES (2018),
    order_2019      VALUES (2019)
)
List (Manual w/ Multiple Columns) 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE example_cts.partition_list_manual_by_year_and_month
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP NOT NULL
)
PARTITION BY LIST (YEAR(purchase_ts), MONTH(purchase_ts))
PARTITIONS
(
    order_2016_0203 VALUES ((2016, 2), (2016, 3)),
    order_2020_03   VALUES ((2020, 3))
)
List (Automatic) 
1
2
3
4
5
6
7
8
9

CREATE TABLE example_cts.partition_list_automatic_by_year_and_month
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP NOT NULL
)
PARTITION BY LIST (YEAR(purchase_ts), MONTH(purchase_ts))
AUTOMATIC
Hash 
1
2
3
4
5
6
7
8
9

CREATE TABLE example_cts.partition_hash
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP NOT NULL
)
PARTITION BY HASH (YEAR(purchase_ts), MONTH(purchase_ts))
PARTITIONS 10
Series 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE example_cts.partition_series
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP NOT NULL,
    SHARD KEY (customer_id)
)
PARTITION BY SERIES (customer_id)
PERCENT_FULL 25

Tier Strategy

See section on the tier strategy clause for details.

Priorities 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE example_cts.tier_strategy_priorities
(
    id           INT NOT NULL,
    name         VARCHAR(128) NOT NULL,
    last_seen    DATETIME
)
TIER STRATEGY
(
    ( ( VRAM 1, RAM 7, PERSIST 5 ) )
)
Disk Cache 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE example_cts.tier_strategy_disk_cache
(
    id           INT NOT NULL,
    name         VARCHAR(128) NOT NULL,
    last_seen    DATETIME
)
TIER STRATEGY
(
    ( ( VRAM 1, RAM 7, DISK0 6, PERSIST 4 ) )
)
Cold Storage 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE example_cts.tier_strategy_cold_storage
(
    id           INT NOT NULL,
    name         VARCHAR(128) NOT NULL,
    last_seen    DATETIME
)
TIER STRATEGY
(
    ( ( VRAM 1, RAM 7, DISK0 6, PERSIST 4, COLD5 5) )
)
Expression-Based 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE example_cts.tier_strategy_expression_based
(
    id           INT NOT NULL,
    name         VARCHAR(128) NOT NULL,
    last_seen    DATETIME
)
TIER STRATEGY
(
    (
        (VRAM 1, RAM 7) WHERE last_seen > TIMESTAMPADD(WEEK, -1, NOW()),
        (RAM 6)
    )
)
Per-Column 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE TABLE example_cts.tier_strategy_column_vs_global
(
    id           INT NOT NULL,
    name         VARCHAR(128) NOT NULL,
    last_seen    DATETIME
)
TIER STRATEGY
(
    (
        (VRAM 1, RAM 9) WHERE last_seen > TIMESTAMPADD(DAY, -3, NOW()),
        (VRAM 1, RAM 8, DISK0 7)
    ),
    (
        COLUMNS id, name
            (VRAM 2, RAM 10) WHERE last_seen > TIMESTAMPADD(MINUTE, -1, NOW())
    )
)