CREATE TABLE¶

Syntax¶

CREATE [ OR REPLACE ]
    [ { [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE | TRANSIENT } ]
  TABLE [ IF NOT EXISTS ] <table_name>

  (
    -- Column definition
    <col_name> <col_type>
      [ inlineConstraint ]
      [ NOT NULL ]
      [ COLLATE '<collation_specification>' ]
      [
        {
          DEFAULT <expr>
          | { AUTOINCREMENT | IDENTITY }
            [
              {
                ( <start_num> , <step_num> )
                | START <num> INCREMENT <num>
              }
            ]
            [ { ORDER | NOORDER } ]
        }
      ]
      [ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ] ]
      [ [ WITH ] PROJECTION POLICY <policy_name> ]
      [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
      [ COMMENT '<string_literal>' ]

    -- Additional column definitions
    [ , <col_name> <col_type> [ ... ] ]

    -- Out-of-line constraints
    [ , outoflineConstraint [ ... ] ]
  )

  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE } ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] AGGREGATION POLICY <policy_name> [ ENTITY KEY ( <col_name> [ , <col_name> ... ] ) ] ]
  [ [ WITH ] JOIN POLICY <policy_name> [ ALLOWED JOIN KEYS ( <col_name> [ , ... ] ) ] ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

Where:

col_name is an object identifier. It must follow the requirements for Snowflake identifiers.

col_type is one of the Snowflake data types, such as NUMBER or VARCHAR.

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

Copy

For additional inline constraint details, see CREATE | ALTER TABLE … CONSTRAINT.

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> ]
  [ COMMENT '<string_literal>' ]

Copy

For additional out-of-line constraint details, see CREATE | ALTER TABLE … CONSTRAINT.

Variant syntax¶

CREATE OR ALTER
    [ { [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | TRANSIENT } ]
  TABLE <table_name> (
    -- Column definition
    <col_name> <col_type>
      [ inlineConstraint ]
      [ NOT NULL ]
      [ COLLATE '<collation_specification>' ]
      [
        {
          DEFAULT <expr>
          | { AUTOINCREMENT | IDENTITY }
            [
              {
                ( <start_num> , <step_num> )
                | START <num> INCREMENT <num>
              }
            ]
            [ { ORDER | NOORDER } ]
        }
      ]
      [ COMMENT '<string_literal>' ]

    -- Additional column definitions
    [ , <col_name> <col_type> [ ... ] ]

    -- Out-of-line constraints
    [ , outoflineConstraint [ ... ] ]
  )
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE } ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ COMMENT = '<string_literal>' ]

Required parameters¶

name

Specifies the identifier (i.e. name) for the table; must be unique for the schema in which the table is created.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier requirements.

col_name

Specifies the column identifier (i.e. name). All the requirements for table identifiers also apply to column identifiers.

For more details, see Identifier requirements and Reserved & limited keywords.

Note

In addition to the standard reserved keywords, the following keywords cannot be used as column identifiers because they are reserved for ANSI-standard context functions:

• CURRENT_DATE

• CURRENT_ROLE

• CURRENT_TIME

• CURRENT_TIMESTAMP

• CURRENT_USER

For the list of reserved keywords, see Reserved & limited keywords.

col_type

Specifies the data type for the column.

For details about the data types that can be specified for table columns, see SQL data types reference.

query

Required for CTAS and USING TEMPLATE.

• For CTAS, specifies the SELECT statement that populates the table.

• For CREATE TABLE … USING TEMPLATE, specifies the subquery that calls the INFER_SCHEMA function and formats the output as an array. Alternatively, USING TEMPLATE accepts the INFER_SCHEMA output as a string literal or variable.

source_table

Required for LIKE and CLONE.

• For CREATE TABLE … LIKE, specifies the table from which properties and column definitions are copied.

• For CREATE TABLE … CLONE, specifies the table to use as the source for the clone.

Optional parameters¶

{ [ { LOCAL | GLOBAL } ] TEMP [ READ ONLY] | . TEMPORARY [ READ ONLY] | . VOLATILE | . TRANSIENT }

Specifies that the table persists only for the duration of the session that you created it in. A temporary table and all its contents are dropped at the end of the session.

The synonyms and abbreviations for TEMPORARY (e.g. GLOBAL TEMPORARY) are provided for compatibility with other databases (e.g. to prevent errors when migrating CREATE TABLE statements). Tables created with any of these keywords appear and behave identically to a table created with the TEMPORARY keyword.

Default: No value. If a table is not declared as TEMPORARY or TRANSIENT, the table is permanent.

If you want to avoid unexpected conflicts, avoid naming temporary tables after tables that already exist in the schema.

If you created a temporary table with the same name as another table in the schema, all queries and operations used on the table only affect the temporary table in the session, until you drop the temporary table. If you drop the table, you drop the temporary table, and not the table that already exists in the schema.

For information about temporary or transient tables, and how they can affect storage and cost, refer to the following resources:

• Working with Temporary and Transient Tables

• Storage Costs for Time Travel and Fail-safe

READ ONLY

Specifies that the table is read-only. READ ONLY is valid only for a temporary table that is being created with the CREATE TABLE … CLONE variant of the CREATE TABLE command.

A read-only table does not allow DML operations and only allows the following subset of DDL operations:

• ALTER TABLE … { ALTER | MODIFY } COLUMN … { SET | UNSET } COMMENT

• ALTER TABLE … { ALTER | MODIFY } COLUMN … { SET | UNSET } MASKING POLICY

• ALTER TABLE … { ALTER | MODIFY } COLUMN … { SET | UNSET } TAG

• ALTER TABLE … RENAME COLUMN … TO

• ALTER TABLE … RENAME TO

• ALTER TABLE … { SET | UNSET } COMMENT

• ALTER TABLE … { SET | UNSET } TAG

• COMMENT

• DESCRIBE

• DROP

• SHOW

• UNDROP

Read-only tables have a METADATA$ROW_POSITION column. This metadata column assigns a row number to each row in the table that is continuous and starts from 0. The row number assigned to each row remains unchanged until the read-only table is dropped.

TRANSIENT

Specifies that the table is transient.

Like a permanent table, a transient table exists until explicitly dropped and is visible to any user with the appropriate privileges. However, transient tables have a lower level of data protection than permanent tables, meaning that data in a transient table might be lost in the event of a system failure. As such, transient tables should only be used for data that can be recreated externally to Snowflake.

Default: No value. If a table is not declared as TRANSIENT or TEMPORARY, the table is permanent.

Note

Transient tables have some storage considerations.

For more information about these and other considerations when deciding whether to create temporary or transient tables, see Working with Temporary and Transient Tables and Storage Costs for Time Travel and Fail-safe.

CONSTRAINT ...

Defines an inline or out-of-line constraint for the specified column(s) in the table.

For syntax details, see CREATE | ALTER TABLE … CONSTRAINT. For more information about constraints, see Constraints.

COLLATE 'collation_specification'

Specifies the collation to use for column operations such as string comparison. This option applies only to text columns (VARCHAR, STRING, TEXT, etc.). For more details, see Collation specifications.

DEFAULT ... or . AUTOINCREMENT ...

Specifies whether a default value is automatically inserted in the column if a value is not explicitly specified via an INSERT or CREATE TABLE AS SELECT statement:

DEFAULT expr

Column default value is defined by the specified expression which can be any of the following:

• Constant value.

• Sequence reference (seq_name.NEXTVAL).

• Simple expression that returns a scalar value.

  The simple expression can include a SQL UDF (user-defined function) if the UDF is not a secure UDF.

  Note

  If a default expression refers to a SQL UDF, then the function is replaced by its definition at table creation time. If the user-defined function is redefined in the future, this does not update the column’s default expression.

  The simple expression cannot contain references to:

  • Subqueries.

  • Aggregates.

  • Window functions.

  • Secure UDFs.

  • UDFs written in languages other than SQL (e.g. Java, JavaScript).

  • External functions.

{ AUTOINCREMENT | IDENTITY } . [ { ( start_num , step_num ) | START num INCREMENT num } ] . [ { ORDER | NOORDER } ]

When you specify AUTOINCREMENT or IDENTITY, the default value for the column starts with a specified number and each successive value automatically increments by the specified amount.

AUTOINCREMENT and IDENTITY are synonymous and can be used only for columns with numeric data types, such as NUMBER, INT, FLOAT.

Caution

Snowflake uses a sequence to generate the values for an auto-incremented column. Sequences have limitations; see Sequence Semantics.

The default value for both the start value and the step/increment value is 1.

Note

Manually inserting values into an AUTOINCREMENT or IDENTITY column can result in duplicate values. If you manually insert the value 5 into an AUTOINCREMENT or IDENTITY column, a subsequently inserted row might use the same value 5 as the default value for the column.

Use ORDER or NOORDER to specify whether or not the values are generated for the auto-incremented column in increasing or decreasing order.

• ORDER specifies that the values generated for a sequence or auto-incremented column are in increasing order (or, if the interval is a negative value, in decreasing order).

  For example, if a sequence or auto-incremented column has START 1 INCREMENT 2, the generated values might be 1, 3, 5, 7, 9, etc.

• NOORDER specifies that the values are not guaranteed to be in increasing order.

  For example, if a sequence has START 1 INCREMENT 2, the generated values might be 1, 3, 101, 5, 103, etc.

  NOORDER can improve performance when multiple INSERT operations are performed concurrently (for example, when multiple clients are executing multiple INSERT statements).

If you do not specify ORDER or NOORDER, the NOORDER_SEQUENCE_AS_DEFAULT parameter determines which property is set.

Note

DEFAULT and AUTOINCREMENT are mutually exclusive; only one can be specified for a column.

MASKING POLICY = policy_name

Specifies the masking policy to set on a column.

This parameter is not supported by the CREATE OR ALTER variant syntax.

PROJECTION POLICY policy_name

Specifies the projection policy to set on a column.

This parameter is not supported by the CREATE OR ALTER variant syntax.

COMMENT 'string_literal'

Specifies a comment for the column.

(Note that comments can be specified at the column level or the table level. The syntax for each is slightly different.)

USING ( col_name , cond_col_1 ... )

Specifies the arguments to pass into the conditional masking policy SQL expression.

The first column in the list specifies the column for the policy conditions to mask or tokenize the data and must match the column to which the masking policy is set.

The additional columns specify the columns to evaluate to determine whether to mask or tokenize the data in each row of the query result when a query is made on the first column.

If the USING clause is omitted, Snowflake treats the conditional masking policy as a normal masking policy.

CLUSTER BY ( expr [ , expr , ... ] )

Specifies one or more columns or column expressions in the table as the clustering key. For more details, see Clustering Keys & Clustered Tables.

Default: No value (no clustering key is defined for the table)

Important

Clustering keys are not intended or recommended for all tables; they typically benefit very large (i.e. multi-terabyte) tables.

Before you specify a clustering key for a table, you should understand micro-partitions. For more information, see Understanding Snowflake Table Structures.

ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE }

Enables or disables automatic changes to the table schema from data loaded into the table from source files, including:

• Added columns.

  By default, schema evolution is limited to a maximum of 10 added columns per load operation. To request more than 10 added columns per load operation, contact Snowflake Support.

• The NOT NULL constraint can be dropped from any number of columns missing in new data files.

Setting it to TRUE enables automatic table schema evolution. The default FALSE disables automatic table schema evolution.

Note

Loading data from files evolves the table columns when all of the following are true:

• The COPY INTO <table> statement includes the MATCH_BY_COLUMN_NAME option.

• The role used to load the data has the EVOLVE SCHEMA or OWNERSHIP privilege on the table.

Additionally, for schema evolution with CSV, when used with MATCH_BY_COLUMN_NAME and PARSE_HEADER, ERROR_ON_COLUMN_COUNT_MISMATCH must be set to false.

DATA_RETENTION_TIME_IN_DAYS = integer

Specifies the retention period for the table so that Time Travel actions (SELECT, CLONE, UNDROP) can be performed on historical data in the table. For more details, see Understanding & using Time Travel and Working with Temporary and Transient Tables.

For a detailed description of this object-level parameter, as well as more information about object parameters, see Parameters.

Values:

• Standard Edition: 0 or 1

• Enterprise Edition:

  • 0 to 90 for permanent tables

  • 0 or 1 for temporary and transient tables

Default:

• Standard Edition: 1

• Enterprise Edition (or higher): 1 (unless a different default value was specified at the schema, database, or account level)

Note

A value of 0 effectively disables Time Travel for the table.

MAX_DATA_EXTENSION_TIME_IN_DAYS = integer

Object parameter that specifies the maximum number of days for which Snowflake can extend the data retention period for the table to prevent streams on the table from becoming stale.

For a detailed description of this parameter, see MAX_DATA_EXTENSION_TIME_IN_DAYS.

CHANGE_TRACKING = { TRUE | FALSE }

Specifies whether to enable change tracking on the table.

• TRUE enables change tracking on the table. This setting adds a pair of hidden columns to the source table and begins storing change-tracking metadata in the columns. These columns consume a small amount of storage.

  The change-tracking metadata can be queried using the CHANGES clause for SELECT statements, or by creating and querying one or more streams on the table.

• FALSE does not enable change tracking on the table.

Default: FALSE

DEFAULT_DDL_COLLATION = 'collation_specification'

Specifies a default collation specification for the columns in the table, including columns added to the table in the future.

For more details about the parameter, see DEFAULT_DDL_COLLATION.

COPY GRANTS

Specifies to retain the access privileges from the original table when a new table is created using any of the following CREATE TABLE variants:

• CREATE OR REPLACE TABLE

• CREATE TABLE … LIKE

• CREATE TABLE … CLONE

The parameter copies all privileges, except OWNERSHIP, from the existing table to the new table. The new table does not inherit any future grants defined for the object type in the schema. By default, the role that executes the CREATE TABLE statement owns the new table.

If the parameter is not included in the CREATE TABLE statement, then the new table does not inherit any explicit access privileges granted on the original table, but does inherit any future grants defined for the object type in the schema.

Note:

• With data sharing:

  • If the existing table was shared to another account, the replacement table is also shared.

  • If the existing table was shared with your account as a data consumer, and access was further granted to other roles in the account (using GRANT IMPORTED PRIVILEGES on the parent database), access is also granted to the replacement table.

• The SHOW GRANTS output for the replacement table lists the grantee for the copied privileges as the role that executed the CREATE TABLE statement, with the current timestamp when the statement was executed.

• The operation to copy grants occurs atomically in the CREATE TABLE command (i.e. within the same transaction).

• This parameter is not supported by the CREATE OR ALTER variant syntax.

COMMENT = 'string_literal'

Specifies a comment for the table.

Default: No value

(Note that comments can be specified at the column level, constraint level, or table level. The syntax for each is slightly different.)

ROW ACCESS POLICY policy_name ON ( col_name [ , col_name ... ] )

Specifies the row access policy to set on a table.

This parameter is not supported by the CREATE OR ALTER variant syntax.

AGGREGATION POLICY policy_name [ ENTITY KEY ( col_name [ , col_name ... ] ) ]

Specifies an aggregation policy to set on a table. You can apply one or more aggregation policies on a table.

Use the optional ENTITY KEY parameter to define which columns uniquely identity an entity within the table. For more information, see Implementing entity-level privacy with aggregation policies. You can specify one or more entity keys for an aggregation policy.

This parameter is not supported by the CREATE OR ALTER variant syntax.

JOIN POLICY policy_name [ ALLOWED JOIN KEYS ( col_name [ , ... ] ) ]

Specifies the join policy to set on a table.

Use the optional ALLOWED JOIN KEYS parameter to define which columns are allowed to be used as joining columns when this policy is in effect. For more information, see Join policies.

This parameter is not supported by the CREATE OR ALTER variant syntax.

TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] )

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas for objects and columns.

This parameter is not supported by the CREATE OR ALTER variant syntax.

Access control requirements¶

A role used to execute this operation must have the following privileges at a minimum:

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

• 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 DROP TABLE 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 Storage Costs for Time Travel and Fail-safe.

    CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

    This means that any queries concurrent with the CREATE OR REPLACE TABLE operation use either the old or new table version.

    Recreating or swapping a table drops its change data. Any stream on the table becomes stale. In addition, any stream on a view that has this table as an underlying table, becomes stale. A stale stream is unreadable.

• The OR REPLACE and IF NOT EXISTS clauses are mutually exclusive. They can’t both be used in the same statement.

• Similar to reserved keywords, ANSI-reserved function names (CURRENT_DATE, CURRENT_TIMESTAMP, etc.) cannot be used as column names.

• CREATE OR ALTER TABLE:

  For more information, see CREATE OR ALTER TABLE usage notes.

• CREATE TABLE … CLONE:

  If the source table has clustering keys, then the new table has clustering keys. By default, Automatic Clustering is suspended for the new table – even if Automatic Clustering was not suspended for the source table.

• CREATE TABLE … CHANGE_TRACKING = TRUE:

  When change tracking is enabled, the table is locked for the duration of the operation. Locks can cause latency with some associated DDL/DML operations. For more information, refer to Resource locking.

• CREATE TABLE … LIKE:

  If the source table has clustering keys, then the new table has clustering keys. By default, Automatic Clustering is not suspended for the new table – even if Automatic Clustering was suspended for the source table.

• CREATE TABLE … AS SELECT (CTAS):

  • If the aliases for the column names in the SELECT list are valid columns, 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 ...
    

    Copy

    Alternatively, the names can be explicitly specified using the following syntax:

    CREATE TABLE <table_name> ( <col1_name> , <col2_name> , ... ) AS SELECT ...
    

    Copy

    The number of column names specified must match the number of SELECT list items in the query; the types of the columns are inferred from the types produced by the query.

  • When clustering keys are specified in a CTAS statement:

    • Column definitions are required and must be explicitly specified in the statement.

    • By default, Automatic Clustering is not suspended for the new table – even if Automatic Clustering is suspended for the source table.

  • If you want the table to be created with rows in a specific order, then use an ORDER BY sub-clause in the SELECT clause of the CTAS. Specifying CLUSTER BY does not cluster the data at the time that the table is created; instead, CLUSTER BY relies on automatic clustering to recluster the data over time.

    The ORDER BY sub-clause in a CREATE TABLE statement does not affect the order of the rows returned by future SELECT statements on that table. To specify the order of rows in future SELECT statements, use an ORDER BY sub-clause in those statements.

• Inside a transaction, any DDL statement (including CREATE TEMPORARY/TRANSIENT TABLE) commits the transaction before executing the DDL statement itself. The DDL statement then runs in its own transaction. The next statement after the DDL statement starts a new transaction. Therefore, you can’t create, use, and drop a temporary or transient table within a single transaction. If you want to use a temporary or transient table inside a transaction, then create the table before the transaction, and drop the table after the transaction.

• Recreating a table (using the optional OR REPLACE keyword) drops its history, which makes any stream on the table stale. A stale stream is unreadable.

• A single masking policy that uses conditional columns can be applied to multiple tables provided that the column structure of the table matches the columns specified in the policy.

• When creating a table with a masking policy on one or more table columns, or a row access policy added to the table, use the POLICY_CONTEXT function to simulate a query on the column(s) protected by a masking policy and the table protected by a row access policy.

• Regarding metadata:

  Attention

  Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

CREATE OR ALTER TABLE usage notes¶

• Limitations

  • Currently only supports permanent, temporary, and transient tables. Read-only, external, dynamic, Apache Iceberg™, and hybrid tables are not supported.

  • All limitations of the ALTER TABLE command apply.

  • Currently does not support the following:

    • CREATE TABLE … AS SELECT (CTAS) variant syntax.

    • CREATE TABLE … USING TEMPLATE variant syntax.

    • CREATE TABLE … LIKE variant syntax.

    • CREATE TABLE … CLONE variant syntax.

• Table parameters and properties

  • The absence of a property or parameter that was previously set in the modified table definition results in unsetting it.

  • Unsetting an explicit parameter value results in setting it to the default parameter value. If the parameter is set on the schema or database that contain the table, the table inherits the parameter value set on the schema or database.

• Data governance

  • Setting or unsetting a tag or policy on a table or column using a CREATE OR ALTER TABLE statement is not supported.

    Existing policies or tags are not altered by a CREATE OR ALTER statement and remain unchanged.

• Constraints

  Setting or unsetting an inline primary key changes the nullability of the column accordingly. This aligns with the behavior of the CREATE TABLE command, but is different from the behavior of the ALTER TABLE command.

• Columns

  • New columns can only be added to the end of the column list.

  • Columns cannot be renamed. If you attempt to rename a column, the column is dropped and a new column is added.

  • The default value for a column can only be modified to use a sequence.

  • The default sequence for a column (for example, SET DEFAULT seq_name.NEXTVAL) can only be changed if the column already has a sequence.

  • For more information about modifying columns, see ALTER TABLE … ALTER COLUMN.

• Collation

  • Collation specifications cannot be altered.

  • Setting the DEFAULT_DDL_COLLATION parameter in the CREATE OR ALTER TABLE command sets the default collation specification for existing columns, which ensures the CREATE OR ALTER TABLE command yields the same results as the CREATE TABLE command. Therefore, you can’t use the CREATE OR ALTER TABLE command to set the DEFAULT_DDL_COLLATION parameter on a table that has existing text columns. You can, however, make collations explicit for existing columns when changing the DEFAULT_DDL_COLLATION parameter for a table.

    For example, create a new table my_table and set the default collation specification for the table to ‘fr’:

    CREATE OR ALTER TABLE my_table (
      a INT PRIMARY KEY,
      b VARCHAR(20)
    )
    DEFAULT_DDL_COLLATION = 'fr';
    

    Copy

    The collation specification for column b is ‘fr’ and cannot be changed. To change the default collation specification for table my_table, you must explicitly set the collation for text column b in the CREATE OR ALTER statement:

    CREATE OR ALTER TABLE my_table (
      a INT PRIMARY KEY,
      b VARCHAR(200) COLLATE 'fr'
    )
    DEFAULT_DDL_COLLATION = 'de';
    

    Copy

• Atomicity

  The CREATE OR ALTER TABLE command currently does not guarantee atomicity. This means that if a CREATE OR ALTER TABLE statement fails during execution, it is possible that a subset of changes might have been applied to the table. If there is a possibility of partial changes, the error message, in most cases, includes the following text:

  CREATE OR ALTER execution failed. Partial updates may have been applied.
  

  For example, if the statement is attempting to drop column A and add a new column B to a table, and the statement is aborted, it is possible that column A was dropped but column B was not added.

  Note

  If changes are partially applied, the resulting table is still in a valid state, and you can use additional ALTER TABLE statements to complete the original set of changes.

  To recover from partial updates, Snowflake recommends the following recovery mechanisms:

  • Fix forward

    • Re-execute the CREATE OR ALTER TABLE statement. If the statements succeeds on the second attempt, the target state is achieved.

    • Investigate the error message. If possible, fix the error and re-execute the CREATE OR ALTER TABLE statement.

  • Roll back

    If it is not possible to fix forward, Snowflake recommends manually rolling back partial changes:

    • Investigate the state of the table using the DESCRIBE TABLE and SHOW TABLES commands. Determine which partial changes were applied, if any.

    • If any partial changes were applied, execute the appropriate ALTER TABLE statements to transform the table back to its original state.

      Note

      In some cases, you might not be able to undo partial changes. For more information, see the supported and unsupported actions for modifying column properties in the ALTER TABLE … ALTER COLUMN topic.

  • If you need help recovering from a partial update, contact Snowflake Support.

Examples¶

CREATE OR ALTER TABLE my_table(a INT);

CREATE OR ALTER TABLE my_table(
    a INT PRIMARY KEY,
    b VARCHAR(200)
  )
  DATA_RETENTION_TIME_IN_DAYS = 5
  DEFAULT_DDL_COLLATION = 'de';

CREATE OR ALTER TABLE my_table(
    a INT PRIMARY KEY,
    c VARCHAR(200)
  )
  DEFAULT_DDL_COLLATION = 'de';