Teradata Alter Table Add Column

Teradata ALTER TABLE ADD COLUMN statement adds a new column to an existing Teradata table without dropping it. Apart from adding a new column, the ADD function has the capabilities to add data types, size, whether or not the new column should allow NULL values, and whether  or not the new column has a default value associated with it.

Teradata Alter Table Add Column Syntax

The syntax of Teradata Alter Table Add Column is as below.

ALTER TABLE database_name.tbl_name ADD column_name datatype;

Teradata Alter Table Add Column Example

Consider an employee table with below definition.

show table Teradatapoint.employee;

CREATE SET TABLE Teradatapoint.employee ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
empid INTEGER,
empname VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC
)
UNIQUE PRIMARY INDEX ( empid );

Now, let’s add 2 new column hobbies and native_place with the following details one by one.

ALTER TABLE Teradatapoint.employee ADD hobbies varchar(50);

ALTER TABLE Teradatapoint.employee ADD hobbies varchar(50) DEFAULT 'Listen to Music';

ALTER TABLE Teradatapoint.employee ADD native_place varchar(50) NOT NULL;

In the first statement, we have added a new column hobbies in the employee table with varchar(50). Next, we have added DEFAULT value for the column as 'Listen to Music'. And finally added another new column native_place with NOT NULL constraint.

Now, if you check the table definition of employee, you will see as below.

show table Teradatapoint.employee;

CREATE SET TABLE Teradatapoint.employee ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
empid INTEGER,
empname VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
hobbies VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT 'Listen to Music',
native_place VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
)
UNIQUE PRIMARY INDEX ( empid );

Summary: In this tutorial, you have learned how to add a new column in Teradata table with different specification.

Also Read:-

1. Modify an existing column in Teradata Table.