ALTER TABLE (Column)

Purpose

Use this to add, drop, change the data type, rename a column, or to define default values and column identities.

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 either the system privilege ALTER ANY TABLE or the object privilege ALTER on the table or its schema, or the table must be owned by you or one of your assigned roles.

Syntax

alter_column::=

ALTER COLUMN Statement

add_column::=

ADD Column statement

drop_column::=

Drop Column Statement

modify_column::=

Modify Column Statement

Modify Column Statement

inline_constraint::=

Inline Contraint

reference_clause::=

Reference Clause

rename_column::=

Rename Column Statement

alter_column_default::=

Alter Column Statement

alter_column_identity::=

Alter Column Identity

Usage Notes

ADD COLUMN

  • If the table already contains rows, the content of the inserted column will be set to the default value if it has been specified. Else, it will be initialized with NULL.
  • If the default value expression is not appropriate for the specified data type, an error message is displayed, and the statement is not executed. For information on the permissible expressions for default value expr, refer to Default Values section.
  • For identity columns, a monotonically increasing number is generated if the table already possesses rows. For more information, refer to the Identity Columns section.
  • If the clause IF NOT EXISTS is specified, then statement does not throw an error message if the column already exists.
  • For information about constraints, refer to the ALTER TABLE (Constraints) section.

DROP COLUMN

  • If a column that you need to delete has foreign key references, then specify the option CASCADE CONSTRAINTS. When you specify this option, the foreign key reference to the column is deleted even when the referencing table does not belong to the current user.
  • If the column that is dropped is a part of the distribution key (see ALTER TABLE (Distribution/Partitioning) for more information), then the distribution keys are dropped and the new rows of the table will be distributed randomly across the cluster nodes.
  • If the column that is dropped is a part of the partitioning key (see ALTER TABLE (Distribution/Partitioning) for more information), then the partitioning keys are dropped and the new rows of the table will not be partitioned any more.
  • If the optional clause IF EXISTS is specified, then the statement does not throw an exception if the column does not exist.

MODIFY COLUMN

  • The data type and the content specified in the columns must be convertible. For example, in data types, BOOL cannot be converted to TIMESTAMP, and for the content of a column, if you have CHAR (3) with the content '123', then it can be converted to DECIMAL, however, if the content is 'ABC', it cannot be converted.
  • A default value must be convertible to the data type. If a default value is not specified, then any old default value that exists and which is appropriate to the new data type will be used. For information on the permissible expression for default value expr, refer to the Default Values section.
  • When you modify an identity column and do not specify the modified column as an identity column, then the modified column still retains the identity column property. However, the data type of the modified column should be appropriate; else an error is displayed. For more information, refer to the Identity Columns sections.
  • By specifying constraints in the MODIFY command, you can only add constraints, but not modify them. However , the only exception is when you specify a NULL constraint, a previously existing NOT NULL constraint is removed. If you do not specify a constraint, then existing constraints are not changed.
  • If the column that is modified is a part of the distribution key (see ALTER TABLE (Distribution/Partitioning) for more information), then the table is redistributed.

RENAME COLUMN

  • This statement will not change the contents of the table.

ALTER COLUMN DEFAULT

  • This statement will not change the contents of the table.
  • A default value must be convertible to the data type.
  • For information on the permissible expression for default value expr, refer to the Default Values section.
  • DROP DEFAULT should be given priority over SET DEFAULT NULL.

ALTER COLUMN IDENTITY

  • The contents of the table is not affected by this statement.
  • When specifying the optional parameter int, the number generator will be set to this number. Else it is set to 0. Starting with the number specified plus one, monotonically increasing numbers are generated for INSERT statements, which do not insert an explicit value to the identity column.
  • Although numbers are generated in a monotonically increasing order, it can include gaps. By the use of this command, you can also "reset" the number generator. For more information, refer to the Identity Columns section.

Examples

ALTER TABLE t ADD COLUMN IF NOT EXISTS new_dec DECIMAL(18,0);
ALTER TABLE t ADD new_char CHAR(10) DEFAULT 'some text';
ALTER TABLE t DROP COLUMN i;
ALTER TABLE t DROP j;
ALTER TABLE t MODIFY (i DECIMAL(10,2));
ALTER TABLE t MODIFY (j VARCHAR(5) DEFAULT 'text');
ALTER TABLE t MODIFY (k INTEGER IDENTITY 1000);
ALTER TABLE t RENAME COLUMN i TO j;
ALTER TABLE t ALTER COLUMN v SET DEFAULT CURRENT_USER;
ALTER TABLE t ALTER COLUMN v DROP DEFAULT;
ALTER TABLE t ALTER COLUMN id SET IDENTITY 1000;
ALTER TABLE t ALTER COLUMN id DROP IDENTITY;