> ## Documentation Index
> Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Creating Tables
> Copy-paste examples of how to create tables with SQL
For the full table creation syntax, see [CREATE TABLE](/content/sql/ddl/create-table#sql-create-table).
## Column Types
For the full set of column types, see [Column Types](/content/sql/ddl/column-types#sql-column-types).
```sql Numeric theme={null}
CREATE TABLE 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
)
```
```sql Text (Simple) theme={null}
CREATE TABLE 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
)
```
```sql Text (Complex) theme={null}
CREATE TABLE column_types_text_complex
(
ip IPV4, -- Native "ipv4" column property on "string" type
ui UUID, -- Native "uuid" type
j JSON -- Native "json" type
)
```
```sql Date/Time theme={null}
CREATE TABLE column_types_date_time
(
td DATE, -- Native "date" type
dt DATETIME, -- Native "datetime" type
tt TIME, -- Native "time" type
ts TIMESTAMP -- Native "timestamp" type
)
```
```sql Binary theme={null}
CREATE TABLE column_types_binary
(
b BLOB -- Native "bytes" type
)
```
```sql Geospatial theme={null}
CREATE TABLE column_types_geospatial
(
geo GEOMETRY, -- Native "wkt" subtype of "string" type
bw BLOB(WKT) -- Native "wkt" subtype of "bytes" type
)
```
```sql Array theme={null}
CREATE TABLE 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
aubi UNSIGNED BIGINT[3], -- Native "array(ulong, 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](/content/sql/ddl/column-properties#sql-column-properties) like
[dictionary encoding](/content/concepts/dictionary_encoding),
[text searchability](/content/concepts/full_text_search), and the initial
value modifiers are shown with all applicable column types.
```sql Nullability theme={null}
CREATE TABLE 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
)
```
```sql Compression theme={null}
CREATE TABLE column_features_compression
(
b BOOLEAN(COMPRESS(LZ4(12))),
i INT(COMPRESS(NONE)),
r REAL(COMPRESS(SNAPPY)),
d DOUBLE(COMPRESS(ZSTD(7))),
dc DECIMAL(18,4,COMPRESS(LZ4)),
v VARCHAR(COMPRESS(ZSTD(3))),
c_min VARCHAR(1,COMPRESS(SNAPPY)),
c_max VARCHAR(256,COMPRESS(ZSTD)),
ip IPV4(COMPRESS(SNAPPY)),
ui UUID(COMPRESS(ZSTD)),
td DATE(COMPRESS(LZ4(1))),
bl BLOB(COMPRESS(NONE)),
geo GEOMETRY(COMPRESS(ZSTD(22))),
bw BLOB(WKT,COMPRESS(SNAPPY)),
ai INT[3](COMPRESS(ZSTD(-1))),
avec VECTOR(3,COMPRESS(ZSTD(17)))
)
```
```sql Dictionary Encoding theme={null}
CREATE TABLE 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)
)
```
```sql Text Search theme={null}
CREATE TABLE 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)
)
```
```sql Initial Values theme={null}
CREATE TABLE 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)
)
```
```sql Update Values theme={null}
CREATE TABLE column_features_update_values
(
td DATE(UPDATE_WITH_NOW),
dt DATETIME(UPDATE_WITH_NOW),
tt TIME(UPDATE_WITH_NOW),
ts TIMESTAMP(UPDATE_WITH_NOW)
)
```
```sql Comment theme={null}
CREATE TABLE 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](/content/concepts/tables#primary-key),
[shard keys](/content/concepts/tables#shard-key), &
[foreign keys](/content/concepts/tables#foreign-key), are applicable to most column types.
See the respective sections for details.
```sql Primary theme={null}
CREATE TABLE 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)
)
```
```sql Soft Primary theme={null}
CREATE TABLE 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)
)
```
```sql On-Disk Primary theme={null}
CREATE TABLE 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')
```
```sql Shard theme={null}
CREATE TABLE 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)
)
```
```sql Primary/Shard theme={null}
CREATE TABLE 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)
)
```
```sql Foreign theme={null}
CREATE TABLE 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 fk_pk(id)
)
```
```sql Composite Primary theme={null}
CREATE TABLE 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)
)
```
```sql Composite Shard theme={null}
CREATE TABLE 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)
)
```
```sql Composite Foreign theme={null}
CREATE TABLE 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 fk_pk_comp(id, dept_id)
)
```
## Indexes
[Column](/content/concepts/indexes#column-index) &
[low-cardinality](/content/concepts/indexes#low-cardinality-index) indexes have wide column
type applicability; while [chunk skip](/content/concepts/indexes#chunk-skip-index),
[geospatial](/content/concepts/indexes#geospatial-index),
[CAGRA](/content/concepts/indexes#cagra-index), & [HNSW](/content/concepts/indexes#hnsw-index) indexes
are shown with all applicable column types. See the respective sections for
details.
```sql Column (Attribute) theme={null}
CREATE TABLE 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)
```
```sql Low-Cardinality theme={null}
CREATE TABLE 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)
```
```sql Chunk Skip theme={null}
CREATE TABLE 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)
```
```sql Geospatial theme={null}
CREATE TABLE 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)
```
```sql CAGRA theme={null}
CREATE TABLE 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')
```
```sql HNSW theme={null}
CREATE TABLE 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)
```sql No Error theme={null}
CREATE TABLE IF NOT EXISTS 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
)
```
```sql Replace theme={null}
CREATE OR REPLACE TABLE 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](/content/concepts/tables#replication),
[sharded](/content/concepts/tables#sharding), &
[randomly sharded](/content/concepts/tables#random-sharding) tables for details.
```sql Replicated theme={null}
CREATE REPLICATED TABLE 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
)
```
```sql Sharded (Explicit via Shard Key) theme={null}
CREATE TABLE 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)
)
```
```sql Sharded (Implicit via Primary Key) theme={null}
CREATE TABLE 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)
)
```
```sql Randomly Sharded theme={null}
CREATE TABLE 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
```sql Memory Only (Temporary) theme={null}
CREATE TEMP TABLE 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
)
```
```sql Chunk Size theme={null}
CREATE TABLE 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)
```
```sql Compression theme={null}
CREATE TABLE properties_compression
(
id TINYINT NOT NULL,
dept_id TINYINT(COMPRESS(LZ4)),
manager_id TINYINT(COMPRESS(NONE)),
first_name VARCHAR(32,COMPRESS(SNAPPY)),
last_name VARCHAR(64,COMPRESS(LZ4(12))),
salary DECIMAL(18,4,COMPRESS(ZSTD(7))),
hire_date DATE
)
USING TABLE PROPERTIES(COMPRESSION_CODEC = 'zstd(22)')
```
```sql TTL theme={null}
CREATE TABLE 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](/content/sql/ddl/create-table#sql-create-table-partition) for details.
```sql Range theme={null}
CREATE TABLE 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)
)
```
```sql Interval theme={null}
CREATE TABLE 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)
)
```
```sql List (Manual) theme={null}
CREATE TABLE 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)
)
```
```sql List (Manual w/ Multiple Columns) theme={null}
CREATE TABLE 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))
)
```
```sql List (Automatic) theme={null}
CREATE TABLE 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
```
```sql Hash theme={null}
CREATE TABLE 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
```
```sql Series theme={null}
CREATE TABLE 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](/content/sql/ddl/create-table#sql-create-table-tier-strategy)
clause for details.
```sql Priorities theme={null}
CREATE TABLE tier_strategy_priorities
(
id INT NOT NULL,
name VARCHAR(128) NOT NULL,
last_seen DATETIME
)
TIER STRATEGY
(
( ( VRAM 1, RAM 7, PERSIST 5 ) )
)
```
```sql Disk Cache theme={null}
CREATE TABLE 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 ) )
)
```
```sql Cold Storage theme={null}
CREATE TABLE 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) )
)
```
```sql Expression-Based theme={null}
CREATE TABLE 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)
)
)
```
```sql Per-Column theme={null}
CREATE TABLE 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())
)
)
```