Skip to main content

CREATE TABLE

CREATE TABLE is the most complicated part of many Databases, you need to:

  • Manually specify the engine
  • Manually specify the indexes
  • And even specify the data partitions or data shard

In Databend, you don't need to specify any of these, one of Databend's design goals is to make it easier to use.

Syntax​

Create Table​

CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    <column_name> <data_type> [ NOT NULL | NULL] [ { DEFAULT <expr> }],
    <column_name> <data_type> [ NOT NULL | NULL] [ { DEFAULT <expr> }],
    ...
) [CLUSTER BY(<expr> [, <expr>, ...] )]

<data_type>:
  TINYINT
| SMALLINT 
| INT
| BIGINT
| FLOAT
| DOUBLE
| DATE
| DATETIME
| TIMESTAMP 
| VARCHAR
| ARRAY
| OBJECT
| VARIANT
tip

Data type reference:

Create Table LIKE​

Creates an empty copy of an existing table, the new table automatically copies all column names, their data types, and their not-null constraints.

Syntax:

CREATE TABLE [IF NOT EXISTS] [db.]table_name
LIKE [db.]origin_table_name

Create Table AS [SELECT query]​

Creates a table and fills it with data computed by a SELECT command.

CREATE TABLE [IF NOT EXISTS] [db.]table_name
LIKE [db.]origin_table_name
AS SELECT query

Column Nullable​

By default, all columns are not nullable(NOT NULL), if you want to specify a column default to NULL, please use:

CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    <column_name> <data_type> NULL,
     ...
)

Let check it out how difference the column is NULL or NOT NULL.

Create a table t_not_null which column with NOT NULL(Databend Column is NOT NULL by default):

CREATE TABLE t_not_null(a INT);
DESC t_not_null;
+-------+-------+------+---------+
| Field | Type  | Null | Default |
+-------+-------+------+---------+
| a     | Int32 | NO   | 0       |
+-------+-------+------+---------+

Create another table t_null column with NULL:

CREATE TABLE t_null(a INT NULL);
DESC t_null;
+-------+-------+------+---------+
| Field | Type  | Null | Default |
+-------+-------+------+---------+
| a     | Int32 | YES  | NULL    |
+-------+-------+------+---------+

Default Values​

DEFAULT <expression>

Specifies a default value inserted in the column if a value is not specified via an INSERT or CREATE TABLE AS SELECT statement.

For example:

CREATE TABLE t_default_value(a TINYINT UNSIGNED, b SMALLINT DEFAULT (a+3), c VARCHAR DEFAULT 'c');

Desc the t_default_value table:

DESC t_default_value;
+-------+--------+------+---------+
| Field | Type   | Null | Default |
+-------+--------+------+---------+
| a     | UInt8  | NO   | 0       |
| b     | Int16  | NO   | (a + 3) |
| c     | String | NO   | c       |
+-------+--------+------+---------+

Insert a value:

INSERT INTO T_default_value(a) VALUES(1);

Check the table values:

SELECT * FROM t_default_value;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    4 | c    |
+------+------+------+

MySQL Compatibility​

Databend’s syntax is difference from MySQL mainly in the data type and some specific index hints.

Examples​

Create Table​

CREATE TABLE test(a BIGINT UNSIGNED, b VARCHAR , c VARCHAR  DEFAULT concat(b, '-b'));
DESC test;
+-------+--------+------+---------------+
| Field | Type   | Null | Default       |
+-------+--------+------+---------------+
| a     | UInt64 | NO   | 0             |
| b     | String | NO   |               |
| c     | String | NO   | concat(b, -b) |
+-------+--------+------+---------------+
INSERT INTO test(a,b) VALUES(888, 'stars');
SELECT * FROM test;
+------+-------+---------+
| a    | b     | c       |
+------+-------+---------+
|  888 | stars | stars-b |
+------+-------+---------+

Create Table Like Statement​

CREATE TABLE test2 LIKE test;
DESC test2;
+-------+--------+------+---------------+
| Field | Type   | Null | Default       |
+-------+--------+------+---------------+
| a     | UInt64 | NO   | 0             |
| b     | String | NO   |               |
| c     | String | NO   | concat(b, -b) |
+-------+--------+------+---------------+
INSERT INTO test2(a,b) VALUES(888, 'stars');
SELECT * FROM test2;
+------+-------+---------+
| a    | b     | c       |
+------+-------+---------+
|  888 | stars | stars-b |
+------+-------+---------+

Create Table As SELECT (CTAS) Statement​

CREATE TABLE test3 AS SELECT * FROM test2;
DESC test3;
+-------+--------+------+---------------+
| Field | Type   | Null | Default       |
+-------+--------+------+---------------+
| a     | UInt64 | NO   | 0             |
| b     | String | NO   |               |
| c     | String | NO   | concat(b, -b) |
+-------+--------+------+---------------+
SELECT * FROM test3;
+------+-------+---------+
| a    | b     | c       |
+------+-------+---------+
|  888 | stars | stars-b |
+------+-------+---------+