Db2 ALTER TABLE ALTER COLUMN

Summary: in this tutorial, you’ll learn how to use the Db2 ALTER TABLE ALTER COLUMN statement to modify column definitions.

Introduction to Db2 ALTER TABLE ALTER COLUMN statement

Sometimes, you may want to modify column definition to cope with the new business requirements such as extend the maximum length of a column or changing the default value of a column to a new one.

Db2 ALTER TABLE ALTER COLUMN allows you to modify the definitions of the existing columns in a table.

The following illustrates the syntax of the ALTER TABLE ALTER COLUMN statement:

ALTER TABLE table_name
    ALTER COLUMN column_name
    modification_type;

In this syntax:

• First, specify the name of the table which you want to perform the change in the ALTER TABLE clause.
• Second, specify the name of the column that you want to modify.
• Third, add a clause as a modification type. Db2 allows you to change the data type of a column, adjust the column length, and change the default value to a new one.

Db2 ALTER TABLE ALTER COLUMN examples

We’ll use the orders table created in the ALTER TABLE ADD COLUMN tutorial for the demonstration.

1) Modifying the length & data type of column examples

The following example uses the ALTER TABLE ALTER COLUMN statement to change the data type of the created_date column from DATE to TIMESTAMP:

ALTER TABLE orders
    ALTER COLUMN created_date
    SET DATA TYPE TIMESTAMP;

To view the columns of the orders table, you use the DESCRIBE TABLE command:

DESCRIBE TABLE orders;

Here is the output:

This statement adds a new column named note to the orders table:

ALTER TABLE orders
ADD COLUMN note VARCHAR(40);

To increase the length of the note column to 255, you use the following statement:

ALTER TABLE orders
    ALTER COLUMN note
    SET DATA TYPE VARCHAR(255);

2) Changing the default value to a new one example

To change the default value of a column to a new value, you use the following syntax:

ALTER TABLE table_name
    ALTER COLUMN column_name
    SET DEFAULT new_default_value

The following example sets the default value of the created_date column to the current timestamp:

ALTER TABLE orders 
    ALTER COLUMN created_date
    SET DEFAULT CURRENT_TIMESTAMP;

In this tutorial, you’ve learned how to use the Db2 ALTER TABLE ALTER COLUMN statement to modify the definition of a column such as data type, length, and default value.