v.21.1New Features

Allow CREATE TABLE AS SELECT with Column Specification

Official CH changelog text
Allow create table as select with columns specification. Example CREATE TABLE t1 (x String) ENGINE = Memory AS SELECT 1;. #18060 (Maksim Kita).
Support for creating tables with explicit column definitions when using CREATE TABLE AS SELECT syntax, allowing users to specify column names and types alongside data insertion.

Why it matters

This feature enables users to define table schema explicitly while creating a table from a query, providing better control over column types and names. It solves the problem of implicitly inferred schema in CREATE TABLE AS SELECT, improving clarity and correctness of table definitions.

How to use it

Use the syntax CREATE TABLE table_name (column_name ColumnType) ENGINE = Engine AS SELECT ...; to create a table with specified columns and populate it from the query result. For example:

CREATE TABLE t1 (x String) ENGINE = Memory AS SELECT 1;

Related resources

PreviousNext