CREATE TABLE¶

CREATE TABLE creates a new table in an existing database.

Tip

To create a table based on the result of a select query, see CREATE TABLE AS.
To create a table based on files like Parquet and ORC, see CREATE EXTERNAL TABLE

Permissions¶

The role must have the CREATE permission at the schema level.

Syntax¶

create_table_statement ::=
    CREATE [ OR REPLACE ] TABLE [schema_name.]table_name (
        { column_def [, ...] }
    )
    ;

schema_name ::= identifier

table_name ::= identifier

column_def :: = { column_name type_name [ default ] [ column_constraint ] }

column_name ::= identifier

column_constraint ::=
    { NOT NULL | NULL }

default ::=
    DEFAULT default_value
    | IDENTITY [ ( start_with [ , increment_by ] ) ]

Parameters¶

Parameter	Description
`OR REPLACE`	Create a new table, and overwrite any existing table by the same name. Does not return an error if the table already exists. `CREATE OR REPLACE` does not check the table contents or structure, only the table name.
`schema_name`	The name of the schema in which to create the table.
`table_name`	The name of the table to create, which must be unique inside the schema.
`column_def`	A comma separated list of column definitions. A minimal column definition includes a name identifier and a datatype. Other column constraints and default values can be added optionally.

Default values¶

The DEFAULT value constraint specifies a value to use if a value isn’t defined in an INSERT or COPY FROM statement.

The value may be either a literal or GETDATE(), which is s evaluated at the time the row is created.

Note

The DEFAULT constraint only applies if the column does not have a value specified in the INSERT or COPY FROM statement. You can still insert a NULL into an nullable column by explicitly inserting NULL. For example, INSERT INTO cool_animals VALUES (1, 'Gnu', NULL).

Syntax¶

column_def :: = { column_name type_name [ default ] [ column_constraint ] }

column_constraint ::=
    { NOT NULL | NULL }

default ::=
    DEFAULT default_value
    | IDENTITY [ ( start_with [ , increment_by ] ) ]

Identity (sequence)¶

Identity columns can be used for generating key values. Some databases call this AUTOINCREMENT.

The identity property on a column guarantees that each new row inserted is generated based on the current seed & increment.

Warning

The identity property on a column does not guarantee uniqueness. The identity value can be bypassed by specifying it in an INSERT command.

Parameter	Description
`start_with`	A value that is used for the very first row loaded into the table.
`increment_by`	Incremental value that is added to the identity value of the previous row that was loaded.

Examples¶

A simple table¶

CREATE TABLE cool_animals (
   id INT NOT NULL,
   name varchar(30) NOT NULL,
   weight FLOAT,
   is_agressive BOOL
);

A table with default values for some columns¶

CREATE TABLE cool_animals (
   id INT NOT NULL,
   name varchar(30) NOT NULL,
   weight FLOAT,
   is_agressive BOOL DEFAULT false NOT NULL
);

Note

The nullable/non-nullable constraint appears at the end, after the default option

A table with an identity (autoincrement) column¶

CREATE TABLE users (
   id BIGINT IDENTITY(0,1) NOT NULL , -- Start with 0, increment by 1
   name VARCHAR(30) NOT NULL,
   country VARCHAR(30) DEFAULT 'Unknown' NOT NULL
);

Note

Identity columns are supported on BIGINT columns.
Identity does not enforce the uniqueness of values. The identity value can be bypassed by specifying it in an INSERT command.

Creating a table from a SELECT query¶

Use a CREATE TABLE AS statement to create a new table from the results of a SELECT query.

CREATE TABLE users_uk AS SELECT * FROM users WHERE country = 'United Kingdom';