CREATE TABLE ​

CREATE TABLE defines a table's columns, value contracts and constraints. A table stores rows until they are changed or deleted; it does not require a ClickHouse table engine declaration.

Syntax ​

CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [schema.]name
(
    column type [column_constraint ...],
    ...
    [, table_constraint ...]
)
[STRICT | WITHOUT ROWID]

CREATE TABLE name AS SELECT ...

STRICT and WITHOUT ROWID can be combined with a comma in either order. CREATE VIRTUAL TABLE and modules such as FTS5 are not supported.

Columns and constraints ​

Native types validate values on writes and casts. SQL columns permit NULL unless declared NOT NULL.

Supported constraints include PRIMARY KEY, UNIQUE, NOT NULL, CHECK, DEFAULT and foreign keys. In an ordinary table, a column declared exactly INTEGER PRIMARY KEY aliases the row identifier; AUTOINCREMENT is supported. A native UInt64 PRIMARY KEY does not have that row-identifier alias contract.

Generated columns can be VIRTUAL or STORED. CREATE TABLE AS SELECT retains projected native declarations and copies values, but does not copy source constraints.

Examples ​

Typed columns and defaults ​

CREATE TABLE events
(
    id INTEGER PRIMARY KEY,
    kind String NOT NULL,
    amount Decimal(18, 2) DEFAULT '0.00'
);
INSERT INTO events(id, kind) VALUES (1, 'view');

SELECT id, kind, amount
FROM events
FORMAT csv;

1,"view",0.00

A generated value ​

CREATE TABLE order_lines
(
    id INTEGER PRIMARY KEY,
    quantity INTEGER CHECK (quantity > 0),
    unit_price INTEGER NOT NULL,
    total INTEGER GENERATED ALWAYS AS (quantity * unit_price) STORED
);
INSERT INTO order_lines(id, quantity, unit_price) VALUES (1, 3, 10);

SELECT id, total
FROM order_lines
FORMAT csv;

1,30

Creating a table from a query ​

CREATE TABLE selected_events AS
SELECT id, kind FROM events WHERE kind = 'view';

SELECT * FROM selected_events
FORMAT csv;

1,"view"

See data types, temporary tables and ALTER TABLE.