CREATE TABLE command in Snowflake - SQL Syntax and Examples

CREATE TABLE Description

Creates a new table in the current/specified schema or replaces an existing table. A table can have multiple columns, with each column definition consisting of a name, data type, and optionally whether the column has:

CREATE TABLE command Syntax

CREATE [ OR REPLACE ] [ { [ LOCAL | GLOBAL ] TEMP[ORARY] | VOLATILE } | TRANSIENT ] TABLE [ IF NOT EXISTS ]
  <table_name>
    ( <col_name> <col_type>
                             [ COLLATE '<collation_specification>' ]
                                /* COLLATE is supported only for text data types (VARCHAR and synonyms) */
                             [ { DEFAULT <expr>
                               | { AUTOINCREMENT | IDENTITY } [ ( <start_num> , <step_num> ) | START <num> INCREMENT <num> ] } ]
                                /* AUTOINCREMENT (or IDENTITY) is supported only for numeric data types (NUMBER, INT, FLOAT, etc.) */
                             [ NOT NULL ]
                             [ inlineConstraint ]
      [ , <col_name> <col_type> [ ... ] ]
      [ , outoflineConstraint ]
      [ , ... ] )
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ STAGE_FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>'
                           | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]
  [ STAGE_COPY_OPTIONS = ( copyOptions ) ]
  [ DATA_RETENTION_TIME_IN_DAYS = <num> ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]

Where:

inlineConstraint ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY | { [ FOREIGN KEY ] REFERENCES <ref_table_name> [ ( <ref_col_name> ) ] } }
  [ <constraint_properties> ]

For additional inline constraint details, see .

outoflineConstraint ::=
  [ CONSTRAINT <constraint_name> ]
  {
     UNIQUE [ ( <col_name> [ , <col_name> , ... ] ) ]
   | PRIMARY KEY [ ( <col_name> [ , <col_name> , ... ] ) ]
   | [ FOREIGN KEY ] [ ( <col_name> [ , <col_name> , ... ] ) ]
                     REFERENCES <ref_table_name> [ ( <ref_col_name> [ , <ref_col_name> , ... ] ) ]
  }
  [ <constraint_properties> ]

For additional out-of-line constraint details, see .

formatTypeOptions ::=
-- If TYPE = CSV
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     RECORD_DELIMITER = '<character>' | NONE
     FIELD_DELIMITER = '<character>' | NONE
     FILE_EXTENSION = '<string>'
     SKIP_HEADER = <integer>
     SKIP_BLANK_LINES = TRUE | FALSE
     DATE_FORMAT = '<string>' | AUTO
     TIME_FORMAT = '<string>' | AUTO
     TIMESTAMP_FORMAT = '<string>' | AUTO
     BINARY_FORMAT = HEX | BASE64 | UTF8
     ESCAPE = '<character>' | NONE
     ESCAPE_UNENCLOSED_FIELD = '<character>' | NONE
     TRIM_SPACE = TRUE | FALSE
     FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | NONE
     NULL_IF = ( '<string>' [ , '<string>' ... ] )
     ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE | FALSE
     REPLACE_INVALID_CHARACTERS = TRUE | FALSE
     VALIDATE_UTF8 = TRUE | FALSE
     EMPTY_FIELD_AS_NULL = TRUE | FALSE
     SKIP_BYTE_ORDER_MARK = TRUE | FALSE
     ENCODING = '<string>' | UTF8
-- If TYPE = JSON
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     FILE_EXTENSION = '<string>'
     ENABLE_OCTAL = TRUE | FALSE
     ALLOW_DUPLICATE = TRUE | FALSE
     STRIP_OUTER_ARRAY = TRUE | FALSE
     STRIP_NULL_VALUES = TRUE | FALSE
     IGNORE_UTF8_ERRORS = TRUE | FALSE
     SKIP_BYTE_ORDER_MARK = TRUE | FALSE
-- If TYPE = AVRO
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
-- If TYPE = ORC
     -- none (no options are supported)
-- If TYPE = PARQUET
     COMPRESSION = AUTO | LZO | SNAPPY | NONE
     BINARY_AS_TEXT = TRUE | FALSE
     SNAPPY_COMPRESSION = TRUE | FALSE
-- If TYPE = XML
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     IGNORE_UTF8_ERRORS = TRUE | FALSE
     PRESERVE_SPACE = TRUE | FALSE
     STRIP_OUTER_ELEMENT = TRUE | FALSE
     DISABLE_SNOWFLAKE_DATA = TRUE | FALSE
     DISABLE_AUTO_CONVERT = TRUE | FALSE
     SKIP_BYTE_ORDER_MARK = TRUE | FALSE

copyOptions ::=
     ON_ERROR = { CONTINUE | SKIP_FILE | SKIP_FILE_<num> | SKIP_FILE_<num>% | ABORT_STATEMENT }
     SIZE_LIMIT = <num>
     PURGE = TRUE | FALSE
     RETURN_FAILED_ONLY = TRUE | FALSE
     MATCH_BY_COLUMN_NAME = CASE_SENSITIVE | CASE_INSENSITIVE | NONE
     ENFORCE_LENGTH = TRUE | FALSE
     TRUNCATECOLUMNS = TRUE | FALSE
     FORCE = TRUE | FALSE

CREATE TABLE command Examples

Create a simple table in the current database and insert a row in the table:

CREATE TABLE mytable (amount NUMBER);

+-------------------------------------+
| status                              |
|-------------------------------------|
| Table MYTABLE successfully created. |
+-------------------------------------+

INSERT INTO mytable VALUES(1);

SHOW TABLES like 'mytable';

+---------------------------------+---------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
| created_on                      | name    | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner        | retention_time |
|---------------------------------+---------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------|
| Mon, 11 Sep 2017 16:32:28 -0700 | MYTABLE | TESTDB        | PUBLIC      | TABLE |         |            |    1 |  1024 | ACCOUNTADMIN | 1              |
+---------------------------------+---------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+

DESC TABLE mytable;

+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name   | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| AMOUNT | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

Create a simple table and specify comments for both the table and the column in the table:

CREATE TABLE example (col1 number comment 'a column comment') COMMENT='a table comment';

+-------------------------------------+
| status                              |
|-------------------------------------|
| Table EXAMPLE successfully created. |
+-------------------------------------+

SHOW TABLES like 'example';

+---------------------------------+---------+---------------+-------------+-------+-----------------+------------+------+-------+--------------+----------------+
| created_on                      | name    | database_name | schema_name | kind  | comment         | cluster_by | rows | bytes | owner        | retention_time |
|---------------------------------+---------+---------------+-------------+-------+-----------------+------------+------+-------+--------------+----------------|
| Mon, 11 Sep 2017 16:35:59 -0700 | EXAMPLE | TESTDB        | PUBLIC      | TABLE | a table comment |            |    0 |     0 | ACCOUNTADMIN | 1              |
+---------------------------------+---------+---------------+-------------+-------+-----------------+------------+------+-------+--------------+----------------+

DESC TABLE example;

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+------------------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment          |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+------------------|
| COL1 | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | a column comment |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+------------------+

Create a table by selecting from an existing table:

CREATE TABLE mytable_copy (b) AS SELECT * from mytable;

DESC TABLE mytable_copy;

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| B    | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

CREATE TABLE mytable_copy2 AS SELECT b+1 AS c FROM mytable_copy;

DESC TABLE mytable_copy2;

+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| C    | NUMBER(39,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

SELECT * FROM mytable_copy2;

+---+
| C |
|---|
| 2 |
+---+

More advanced example of creating a table by selecting from an existing table; in this example, the values in the summary_amount column in the new table are derived from two columns in the source table:

CREATE TABLE testtable-summary (name, summary_amount) AS SELECT name, amount1 + amount2 FROM testtable;

Create a table by selecting columns from a staged Parquet data file:

CREATE OR REPLACE TABLE parquet_col (
  custKey number default NULL,
  orderDate date default NULL,
  orderStatus varchar(100) default NULL,
  price varchar(255)
)
AS SELECT
  $1:o_custkey::number,
  $1:o_orderdate::date,
  $1:o_orderstatus::text,
  $1:o_totalprice::text
FROM @my_stage;

+-----------------------------------------+
| status                                  |
|-----------------------------------------|
| Table PARQUET_COL successfully created. |
+-----------------------------------------+

DESC TABLE parquet_col;

+-------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name        | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|-------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| CUSTKEY     | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| ORDERDATE   | DATE         | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| ORDERSTATUS | VARCHAR(100) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| PRICE       | VARCHAR(255) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+-------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

Create a table with the same column definitions as another table, but with no rows:

CREATE TABLE mytable (amount NUMBER);

INSERT INTO mytable VALUES(1);

SELECT * FROM mytable;

+--------+
| AMOUNT |
|--------|
|      1 |
+--------+

CREATE TABLE mytable_2 LIKE mytable;

DESC TABLE mytable_2;

+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name   | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| AMOUNT | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+--------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

SELECT * FROM mytable_2;

+--------+
| AMOUNT |
|--------|
+--------+

Create a table with a multi-column clustering key:

CREATE TABLE mytable (date timestamp_ntz, id number, content variant) CLUSTER BY (date, id);

SHOW TABLES LIKE 'mytable';

+---------------------------------+---------+---------------+-------------+-------+---------+------------------+------+-------+--------------+----------------+
| created_on                      | name    | database_name | schema_name | kind  | comment | cluster_by       | rows | bytes | owner        | retention_time |
|---------------------------------+---------+---------------+-------------+-------+---------+------------------+------+-------+--------------+----------------|
| Mon, 11 Sep 2017 16:20:41 -0700 | MYTABLE | TESTDB        | PUBLIC      | TABLE |         | LINEAR(DATE, ID) |    0 |     0 | ACCOUNTADMIN | 1              |
+---------------------------------+---------+---------------+-------------+-------+---------+------------------+------+-------+--------------+----------------+

Specify collation for columns in a table:

CREATE TABLE collation_demo (
  uncollated_phrase VARCHAR, 
  utf8_phrase VARCHAR COLLATE 'utf8',
  english_phrase VARCHAR COLLATE 'en',
  spanish_phrase VARCHAR COLLATE 'sp'
  );

INSERT INTO collation_demo (uncollated_phrase, utf8_phrase, english_phrase, spanish_phrase) 
   VALUES ('pinata', 'pinata', 'pinata', 'piñata');

CREATE TABLE command Usage

A schema cannot contain tables and/or views with the same name. When creating a table:
- If a view with the same name already exists in the schema, an error is returned and the table is not created.
- If a table with the same name already exists in the schema, an error is returned and the table is not created, unless the optional OR REPLACE keyword is included in the command.
Important

Using OR REPLACE is the equivalent of using on the existing table and then creating a new table with the same name; however, the dropped table is not permanently removed from the system. Instead, it is retained in Time Travel. This is important to note because dropped tables in Time Travel can be recovered, but they also contribute to data storage for your account. For more information, see .
Similar to , etc.) cannot be used as column names.
CREATE TABLE … AS SELECT (CTAS):
- If the aliases for the column names in the list are valid column, then the column definitions are not required in the CTAS statement; if omitted, the column names and types are inferred from the underlying query:
  CREATE TABLE <table_name> AS SELECT ...
  Alternatively, the names can be explicitly specified using the following syntax:
  CREATE TABLE <table_name> ( <col1_name> , <col2_name> , ... ) AS SELECT ...
  The number of column names specified must match the number of list items in the query; the types of the columns are inferred from the types produced by the query.
- Clustering keys can be used in a CTAS statement; however, if clustering keys are specified, column definitions are required and must be explicitly specified in the statement.

Convert your code online to Snowflake

Convert Teradata to Snowflake Convert TD to BigQuery