ALTER TABLE … ALTER COLUMN (original) (raw)

This topic describes how to modify one or more column properties for a table using an ALTER COLUMN clause in aALTER TABLE statement.

The following table describes the supported/unsupported actions for modifying column properties:

See also:

ALTER TABLE , CREATE TABLE , DROP TABLE , SHOW TABLES , DESCRIBE TABLE

Syntax¶

ALTER TABLE { ALTER | MODIFY } [ ( ] [ COLUMN ] DROP DEFAULT , [ COLUMN ] SET DEFAULT .NEXTVAL , [ COLUMN ] { [ SET ] NOT NULL | DROP NOT NULL } , [ COLUMN ] [ [ SET DATA ] TYPE ] , [ COLUMN ] COMMENT '' , [ COLUMN ] UNSET COMMENT [ , [ COLUMN ] ... ] [ , ... ] [ ) ]

ALTER TABLE { ALTER | MODIFY } [ COLUMN ] dataGovnPolicyTagAction

Usage notes¶

• A single ALTER TABLE statement can be used to modify multiple columns in a table. Each change is specified as a clause consisting of the column and column property to modify, separated by commas:

  • Use either the ALTER or MODIFY keyword to initiate the list of clauses (i.e. columns/properties to modify) in the statement.
  • Parentheses can be used for grouping the clauses, but are not required.
  • The COLUMN keyword can be specified in each clause, but is not required.
  • The clauses can be specified in any order.

• When setting a column to NOT NULL, if the column contains NULL values, an error is returned and no changes are applied to the column.
• Columns that use semi-structured data types (ARRAY, OBJECT, and VARIANT) cannot be set to NOT NULL, except when the table is empty. Setting these columns to NOT NULL when the table contains rows is not supported and results in an error.
• To change the default sequence for a column, the column must already have a default sequence. You cannot use the commandALTER TABLE ... SET DEFAULT <seq_name> to add a sequence to a column that does not already have a sequence.
• If you alter a table to add a column with a DEFAULT value, then you cannot drop the default value for that column. For example, in the following sequence of statements, the last ALTER TABLE ... ALTER COLUMN statement causes an error:
  CREATE TABLE t(x INT);
  INSERT INTO t VALUES (1), (2), (3);
  ALTER TABLE t ADD COLUMN y INT DEFAULT 100;
  INSERT INTO t(x) VALUES (4), (5), (6);
  ALTER TABLE t ALTER COLUMN y DROP DEFAULT;
  This restriction prevents inconsistency between values in rows inserted before the column was added and rows inserted after the column was added. If the default were dropped, then the column would contain:
  • A NULL value for rows inserted before the column was added.
  • The default value for rows inserted after the column was added.
    Dropping the default column value from any clone of the table is also prohibited.
• When setting the TYPE for a column, the specified type (i.e. _type_) must beNUMBER or a text data type (VARCHAR, STRING, TEXT, etc.).
  • For the NUMBER data type, TYPE can be used to:
    * Increase the precision of the specified number column.
    * Decrease the precision of the specified number column if the new precision is sufficient to hold all data values currently in the column.
  • For text data types, TYPE can be used only to increase the length of the column.
• If the precision of a column is decreased below the maximum precision of any column data retained in Time Travel, you cannot restore the table without first increasing the precision.
• For masking policies:
  • The USING clause and the FORCE keyword are both optional; neither are required to set a masking policy on a column. TheUSING clause and the FORCE keyword can be used separately or together. For details, see:
    * Apply a conditional masking policy on a column
    * Replace a masking policy on a column
  • 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 modifying one or more table columns with a masking policy or the table itself with a row access policy, use thePOLICY_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 (for example, the COMMENT field):
  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.

Examples¶

Example setup:

CREATE OR REPLACE TABLE t1 ( c1 NUMBER NOT NULL, c2 NUMBER DEFAULT 3, c3 NUMBER DEFAULT seq1.nextval, c4 VARCHAR(20) DEFAULT 'abcde', c5 STRING);

DESC TABLE t1;

+------+-------------------+--------+-------+-------------------------+-------------+------------+-------+------------+---------+ | name | type | kind | null? | default | primary key | unique key | check | expression | comment | |------+-------------------+--------+-------+-------------------------+-------------+------------+-------+------------+---------| | C1 | NUMBER(38,0) | COLUMN | N | NULL | N | N | NULL | NULL | NULL | | C2 | NUMBER(38,0) | COLUMN | Y | 3 | N | N | NULL | NULL | NULL | | C3 | NUMBER(38,0) | COLUMN | Y | DB1.PUBLIC.SEQ1.NEXTVAL | N | N | NULL | NULL | NULL | | C4 | VARCHAR(20) | COLUMN | Y | 'abcde' | N | N | NULL | NULL | NULL | | C5 | VARCHAR(16777216) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | +------+-------------------+--------+-------+-------------------------+-------------+------------+-------+------------+---------+

Make the following changes to t1:

• Change NOT NULL column c1 to NULL.
• Drop the default for column c2 and change the default sequence for column c3.
• Increase the length of column c4 and drop the default for the column.
• Add a comment for column c5.

ALTER TABLE t1 ALTER COLUMN c1 DROP NOT NULL;

ALTER TABLE t1 MODIFY c2 DROP DEFAULT, c3 SET DEFAULT seq5.nextval ;

ALTER TABLE t1 ALTER c4 SET DATA TYPE VARCHAR(50), COLUMN c4 DROP DEFAULT;

ALTER TABLE t1 ALTER c5 COMMENT '50 character column';

DESC TABLE t1;

+------+-------------------+--------+-------+-------------------------+-------------+------------+-------+------------+---------------------+ | name | type | kind | null? | default | primary key | unique key | check | expression | comment | |------+-------------------+--------+-------+-------------------------+-------------+------------+-------+------------+---------------------| | C1 | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | | C2 | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | | C3 | NUMBER(38,0) | COLUMN | Y | DB1.PUBLIC.SEQ5.NEXTVAL | N | N | NULL | NULL | NULL | | C4 | VARCHAR(50) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | | C5 | VARCHAR(16777216) | COLUMN | Y | NULL | N | N | NULL | NULL | 50 character column | +------+-------------------+--------+-------+-------------------------+-------------+------------+-------+------------+---------------------+

Same as previous example, but with the following changes to illustrate the versatility/flexibility of the command:

• All actions executed in a single ALTER COLUMN clause.
• The order of the columns within the clause is different.
• SET DATA TYPE shortened to simply TYPE.

ALTER TABLE t1 ALTER ( c1 DROP NOT NULL, c5 COMMENT '50 character column', c4 TYPE VARCHAR(50), c2 DROP DEFAULT, COLUMN c4 DROP DEFAULT, COLUMN c3 SET DEFAULT seq5.nextval );

This example produces the same results.

Apply a Column-level Security masking policy to a table column:

-- single column

ALTER TABLE empl_info MODIFY COLUMN empl_id SET MASKING POLICY mask_empl_id;

-- multiple columns

ALTER TABLE empl_info MODIFY COLUMN empl_id SET MASKING POLICY mask_empl_id , COLUMN empl_dob SET MASKING POLICY mask_empl_dob ;

Unset a Column-level Security masking policy from a table column:

-- single column

ALTER TABLE empl_info modify column empl_id unset masking policy;

-- multiple columns

ALTER TABLE empl_info MODIFY COLUMN empl_id UNSET MASKING POLICY , COLUMN empl_dob UNSET MASKING POLICY ;