CREATE TABLE
Creating tables is one of the most complicated operations for many databases because you might need to:
- Manually specify the engine
- Manually specify the indexes
- And even specify the data partitions or data shard
Databend aims to be easy to use by design and does NOT require any of those operations when you create a table. Moreover, the CREATE TABLE statement provides these options to make it much easier for you to create tables in various scenarios:
- CREATE TABLE: Creates a table from scratch.
- CREATE TABLE ... LIKE: Creates a table with the same column definitions as an existing one.
- CREATE TABLE ... AS: Creates a table and inserts data with the results of a SELECT query.
See also:
CREATE TABLE
CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] [ <database_name>. ]<table_name>
(
<column_name> <data_type> [ NOT NULL | NULL ]
[ { DEFAULT <expr>
| { AUTOINCREMENT | IDENTITY }
[ { ( <start_num> , <step_num> )
| START <num> INCREMENT <num> } ]
[ { ORDER | NOORDER } ]
} ]
[ AS (<expr>) STORED | VIRTUAL ]
[ COMMENT '<comment>' ],
<column_name> <data_type> ...
...
)
[ ENGINE = FUSE ]
[ PARTITION BY ( <expr> [ , <expr> ... ] ) ]
[ CLUSTER BY ( <expr> [ , <expr> ... ] ) ]
[ <fuse_engine_option> = <value> ... ]
Fuse Table Partitioning
PARTITION BY physically separates a Fuse table using one or more partition expressions. Databend preserves partition boundaries during writes and maintenance operations and uses partition metadata to prune unrelated data before scanning.
CREATE TABLE events (
event_id BIGINT,
event_time TIMESTAMP,
tenant_id BIGINT
)
PARTITION BY (DATE_TRUNC(day, event_time))
CLUSTER BY (tenant_id, event_time);
Each expression must be deterministic and reference exactly one source column. Specify multiple expressions for a composite key:
PARTITION BY (DATE_TRUNC(month, event_time), BUCKET(16, tenant_id))
For high-cardinality keys, BUCKET() maps values to a fixed number of hash partitions. WRITE_DISTRIBUTION_MODE = 'hash' can additionally route identical partition values to the same writer during distributed ingestion:
CREATE TABLE customer_events (
customer_id BIGINT,
event_time TIMESTAMP
)
PARTITION BY (BUCKET(32, customer_id))
WRITE_DISTRIBUTION_MODE = 'hash';
The hash write mode requires PARTITION BY; its default value is 'none'. See Partitioning Fuse Tables for strategy selection, limitations, and complete examples.
-
For available data types in Databend, see Data Types.
-
Databend suggests avoiding special characters as much as possible when naming columns. However, if special characters are necessary in some cases, the alias should be enclosed in backticks, like this: CREATE TABLE price(`$CA` int);
-
Databend will automatically convert column names into lowercase. For example, if you name a column as Total, it will appear as total in the result.
CREATE TABLE ... LIKE
Creates a table with the same column definitions as an existing table. Column names, data types, and their non-NUll constraints of the existing will be copied to the new table.
Syntax:
CREATE TABLE [IF NOT EXISTS] [db.]table_name
LIKE [db.]origin_table_name
This command does not include any data or attributes (such as CLUSTER BY, TRANSIENT, and COMPRESSION) from the original table, and instead creates a new table using the default system settings.
TRANSIENTandCOMPRESSIONcan be explicitly specified when you create a new table with this command. For example,
create transient table t_new like t_old;
create table t_new compression='lz4' like t_old;
CREATE TABLE ... AS
Creates a table and fills it with data computed by a SELECT command.
Syntax:
CREATE TABLE [IF NOT EXISTS] [db.]table_name
AS SELECT query
This command does not include any attributes (such as CLUSTER BY, TRANSIENT, and COMPRESSION) from the original table, and instead creates a new table using the default system settings.
TRANSIENTandCOMPRESSIONcan be explicitly specified when you create a new table with this command. For example,
create transient table t_new as select * from t_old;
create table t_new compression='lz4' as select * from t_old;
Column Nullable
By default, all columns are nullable(NULL) in Databend. If you need a column that does not allow NULL values, use the NOT NULL constraint. For more information, see NULL Values and NOT NULL Constraint.
Column Default Values
DEFAULT <expr> sets a default value for the column when no explicit expression is provided. The default expression can be:
- A fixed constant, such as
Marketingfor thedepartmentcolumn in the example below. - An expression with no input arguments and returns a scalar value, such as
1 + 1,NOW()orUUID(). - A dynamically generated value from a sequence, such as
NEXTVAL(staff_id_seq)for thestaff_idcolumn in the example below.- NEXTVAL must be used as a standalone default value; expressions like
NEXTVAL(seq1) + 1are not supported. - Users must adhere to their granted permissions for sequence utilization, including operations such as NEXTVAL
- NEXTVAL must be used as a standalone default value; expressions like
Auto-Increment Columns
AUTOINCREMENT or IDENTITY can be used to create auto-incrementing columns that automatically generate sequential numeric values. This is particularly useful for creating unique identifiers.
Syntax:
{ AUTOINCREMENT | IDENTITY }
[ { ( <start_num> , <step_num> )
| START <num> INCREMENT <num> } ]
[ { ORDER | NOORDER } ]
Parameters:
start_num: The initial value for the auto-increment sequence (default: 1)step_num: The increment value for each new row (default: 1)ORDER: Guarantees monotonically increasing values (with potential gaps)NOORDER: Does not guarantee order (default)
Key Points:
- Auto-increment columns are internally backed by a sequence
- When a column with AUTOINCREMENT/IDENTITY is dropped, its associated sequence is also dropped
- If no explicit value is provided during insertion, the next value is automatically generated
- Both
AUTOINCREMENTandIDENTITYare synonyms and behave identically
Example:
-- Create a table with auto-increment columns
CREATE TABLE users (
user_id BIGINT AUTOINCREMENT,
order_id BIGINT AUTOINCREMENT START 100 INCREMENT 10,
username VARCHAR
);
-- Insert data without specifying auto-increment columns
INSERT INTO users (username) VALUES ('alice'), ('bob'), ('charlie');
-- Query the table to see auto-generated values
SELECT * FROM users;
+----------+----------+----------+
| user_id | order_id | username |
+----------+----------+----------+
| 0 | 100 | alice |
| 1 | 110 | bob |
| 2 | 120 | charlie |
+----------+----------+----------+
Computed Columns
Computed columns are generated from other columns using scalar expressions. Databend supports two types:
- STORED: Values are physically stored and automatically updated when dependent columns change
- VIRTUAL: Values are calculated on-the-fly during queries, saving storage space
Syntax:
<column_name> <data_type> [ NOT NULL | NULL ] AS (<expr>) { STORED | VIRTUAL }
<column_name> <data_type> [ NOT NULL | NULL ] GENERATED ALWAYS AS (<expr>) { STORED | VIRTUAL }
Examples:
-- Stored: physically stored, updates immediately
CREATE TABLE products (
id INT,
price FLOAT64,
quantity INT,
total_price FLOAT64 AS (price * quantity) STORED
);
-- Virtual: computed on query, no storage overhead
CREATE TABLE employees (
id INT,
first_name VARCHAR,
last_name VARCHAR,
full_name VARCHAR AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
);
Choose STORED for frequently queried columns where performance matters. Choose VIRTUAL to save storage space when computation cost is acceptable.
MySQL Compatibility
Databend's syntax is difference from MySQL mainly in the data type and some specific index hints.
Unlike MySQL, Databend follows PostgreSQL-style identifier casing by default: unquoted column names are folded to lowercase, while double-quoted names preserve their case and are case-sensitive. This is why a table created with quoted, case-preserving column names (e.g. "Employee_ID") can return rows for SELECT * but fail for SELECT Employee_ID or SELECT employee_id. See SQL Identifiers for the casing rules, the related settings, and how to troubleshoot this behavior.
Access control requirements
| Privilege | Object Type | Description |
|---|---|---|
| CREATE | Global, Table | Creates a table. |
To create a table, the user performing the operation or the current_role must have the CREATE privilege.
Examples
Create Table
Create a table with a default value for a column (in this case, the genre column has 'General' as the default value):
CREATE TABLE books (
id BIGINT UNSIGNED,
title VARCHAR,
genre VARCHAR DEFAULT 'General'
);
Describe the table to confirm the structure and the default value for the genre column:
DESC books;
+-------+-----------------+------+---------+-------+
| Field | Type | Null | Default | Extra |
+-------+-----------------+------+---------+-------+
| id | BIGINT UNSIGNED | YES | 0 | |
| title | VARCHAR | YES | "" | |
| genre | VARCHAR | YES | 'General'| |
+-------+-----------------+------+---------+-------+
Insert a row without specifying the genre:
INSERT INTO books(id, title) VALUES(1, 'Invisible Stars');
Query the table and notice that the default value 'General' has been set for the genre column:
SELECT * FROM books;
+----+----------------+---------+
| id | title | genre |
+----+----------------+---------+
| 1 | Invisible Stars| General |
+----+----------------+---------+
Create Table ... Like
Create a new table (books_copy) with the same structure as an existing table (books):
CREATE TABLE books_copy LIKE books;
Check the structure of the new table:
DESC books_copy;
+-------+-----------------+------+---------+-------+
| Field | Type | Null | Default | Extra |
+-------+-----------------+------+---------+-------+
| id | BIGINT UNSIGNED | YES | 0 | |
| title | VARCHAR | YES | "" | |
| genre | VARCHAR | YES | 'General'| |
+-------+-----------------+------+---------+-------+
Insert a row into the new table and notice that the default value for the genre column has been copied:
INSERT INTO books_copy(id, title) VALUES(1, 'Invisible Stars');
SELECT * FROM books_copy;
+----+----------------+---------+
| id | title | genre |
+----+----------------+---------+
| 1 | Invisible Stars| General |
+----+----------------+---------+
Create Table ... As
Create a new table (books_backup) that includes data from an existing table (books):
CREATE TABLE books_backup AS SELECT * FROM books;
Describe the new table and notice that the default value for the genre column has NOT been copied:
DESC books_backup;
+-------+-----------------+------+---------+-------+
| Field | Type | Null | Default | Extra |
+-------+-----------------+------+---------+-------+
| id | BIGINT UNSIGNED | NO | 0 | |
| title | VARCHAR | NO | "" | |
| genre | VARCHAR | NO | NULL | |
+-------+-----------------+------+---------+-------+
Query the new table and notice that the data from the original table has been copied:
SELECT * FROM books_backup;
+----+----------------+---------+
| id | title | genre |
+----+----------------+---------+
| 1 | Invisible Stars| General |
+----+----------------+---------+
Create Table ... Column As STORED | VIRTUAL
The following example demonstrates a table with a stored computed column that automatically recalculates based on updates to the "price" or "quantity" columns:
-- Create the table with a stored computed column
CREATE TABLE IF NOT EXISTS products (
id INT,
price FLOAT64,
quantity INT,
total_price FLOAT64 AS (price * quantity) STORED
);
-- Insert data into the table
INSERT INTO products (id, price, quantity)
VALUES (1, 10.5, 3),
(2, 15.2, 5),
(3, 8.7, 2);
-- Query the table to see the computed column
SELECT id, price, quantity, total_price
FROM products;
---
+------+-------+----------+-------------+
| id | price | quantity | total_price |
+------+-------+----------+-------------+
| 1 | 10.5 | 3 | 31.5 |
| 2 | 15.2 | 5 | 76.0 |
| 3 | 8.7 | 2 | 17.4 |
+------+-------+----------+-------------+
In this example, we create a table called studentprofiles with a Variant type column named profile to store JSON data. We also add a virtual computed column named _age that extracts the age property from the profile column and casts it to an integer.
-- Create the table with a virtual computed column
CREATE TABLE student_profiles (
id STRING,
profile VARIANT,
age INT NULL AS (profile['age']::INT) VIRTUAL
);
-- Insert data into the table
INSERT INTO student_profiles (id, profile) VALUES
('d78236', '{"id": "d78236", "name": "Arthur Read", "age": "16", "school": "PVPHS", "credits": 120, "sports": "none"}'),
('f98112', '{"name": "Buster Bunny", "age": "15", "id": "f98112", "school": "TEO", "credits": 67, "clubs": "MUN"}'),
('t63512', '{"name": "Ernie Narayan", "school" : "Brooklyn Tech", "id": "t63512", "sports": "Track and Field", "clubs": "Chess"}');
-- Query the table to see the computed column
SELECT * FROM student_profiles;
+--------+------------------------------------------------------------------------------------------------------------+------+
| id | profile | age |
+--------+------------------------------------------------------------------------------------------------------------+------+
| d78236 | `{"age":"16","credits":120,"id":"d78236","name":"Arthur Read","school":"PVPHS","sports":"none"}` | 16 |
| f98112 | `{"age":"15","clubs":"MUN","credits":67,"id":"f98112","name":"Buster Bunny","school":"TEO"}` | 15 |
| t63512 | `{"clubs":"Chess","id":"t63512","name":"Ernie Narayan","school":"Brooklyn Tech","sports":"Track and Field"}` | NULL |
+--------+------------------------------------------------------------------------------------------------------------+------+