CREATE TABLE

Purpose

This statement creates a new table. You can create a table by either specifying the columns (names and types), or by inheriting the columns of another table, or by assigning a subquery.

Prerequisites

• You need either the system privilege USE ANY SCHEMA or the object privilege USAGE on the target schema, or the schema must be owned by you or one of your assigned roles.

• You need the system privilege CREATE ANY TABLE, or CREATE TABLE if the table is created in a schema owned by you or one of your assigned roles.
• To create tables using a subquery, you need the appropriate SELECT privilege for all objects referenced in the subquery.
• You need the DROP TABLE privilege if you want to replace an existing table using the OR REPLACE option.

Usage Notes

• The OR REPLACE option can be used to replace an already existing table without having to explicitly delete it using DROP TABLE.
• If you have specified the option IF NOT EXISTS, then no error message is thrown if the table already exists.
• If you use the option WITH NO DATA within the CREATE TABLE AS statement, then this query will not be executed. However, an empty result table with the same data types will be defined.
• When a table is created using a subquery (CREATE TABLE AS), and references to the columns is in an expression, then you must specify aliases for these columns.
• Alternatively to the CREATE TABLE AS statement, you can also use the SELECT INTO TABLE syntax. For information, see SELECT INTO section.
• Using the LIKE clause, you can create an empty table with the same data types like in the defined sample table or view. If you use a sample table and you specify the option INCLUDING DEFAULTS, INCLUDING IDENTITY or INCLUDING COMMENTS, then all default values, its identity column, and its column comments are adopted. If you use a sample view and you specify the option INCLUDING COMMENTS, then all column comments are adopted.

If you specify the like_clause without specifying the distribute_by clause, then the distribution key is implicitly adopted from the derived tables, if all distribution key columns of one table and no other distribution key columns of other tables were selected. The same applies to the partition_by clause.

Constraints on primary and foreign keys in the sample table will not be applied to the new table.

• Like expressions and normal column definition can be combined. However, it must be enclosed in brackets.
• The distribute_by and partition_by clauses define the explicit data distribution between the cluster nodes and within the nodes. For more information, refer to ALTER TABLE (Distribution/Partitioning).
• Comments on tables and columns can be set or changed at a later time by using the command COMMENT.
• For information on the data types that you can use in tables, refer to Data Types section.
• For information about constraints in tables , refer to the ALTER TABLE (Constraints) section.
• A default value specified for the column must be convertible to the data type of the column. For more information on the permitted expressions for default values, refer to Default Values section.
• For information on identity columns, refer to the Identity Columns section.

Example

CREATE TABLE t1 (a VARCHAR(20),
           b DECIMAL(24,4) NOT NULL,
           c DECIMAL DEFAULT 122,
           d DOUBLE,
           e TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
           f BOOL);
           
CREATE TABLE t2 AS SELECT * FROM t1;

CREATE OR REPLACE TABLE t2 AS SELECT a,b,c+1 AS c FROM t1;

CREATE TABLE t3 AS SELECT count(*) AS my_count FROM t1 WITH NO DATA;

CREATE TABLE t4 LIKE t1;

CREATE TABLE t5 (id int IDENTITY PRIMARY KEY,
           LIKE t1 INCLUDING DEFAULTS,
           g DOUBLE,
           DISTRIBUTE BY a,b);

CREATE TABLE t6 (order_id INT,
           order_price DOUBLE,
           order_date DATE,
           country VARCHAR(40),
           PARTITION BY order_date);

SELECT * INTO TABLE t7 FROM t1;

CREATE OR REPLACE TABLE t8 (ref_id int CONSTRAINT FK_T5 REFERENCES t5 (id) DISABLE, b VARCHAR(20));