ADD column_name - Advanced SQL Engine - Teradata Database

Add the column or modify the column attributes.

ADD column_name modifies the column, if the table already contains the column or creates the column, if the table does not contain the column. 


column_name

The name of a column to add or change. 
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.


You cannot ADD a column that has the same name as named collected statistics.

You cannot add an identity column to an existing table, nor can you add the identity column attribute to an existing column.

ADD and DROP cannot both be specified on the same column in the same ALTER TABLE request.

data type

To add a column, you must specify a data type. To add a new column and, in certain cases, modify the data type and column attributes of an existing column, use this syntax:

  ADD  column_name 
 data type column attributes

You can add a column with the NUMBER data type, increase the precision of an existing fixed NUMBER column, or increase the precision and scale of a fixed NUMBER data type. You can only modify the scale and precision for fixed NUMBER columns.

For a floating NUMBER column, you cannot perform the following modifications:

• Decrease the precision.
• Decrease the scale.
• Increase the scale without also increasing the precision.
• Increase the scale and precision by different amounts.

If you do not specify explicit formatting, the new column assumes the default format for the data type, which can be specified by a custom data formatting specification (SDF) defined by the tdlocaledef utility. See Teradata Vantage™ - Database Utilities , B035-1102 .

Explicit formatting applies to the parsing and to the retrieval of character strings.

For information on data types and data type attributes, see Teradata Vantage™ - Data Types and Literals, B035-1143. For information on column storage and constraints attributes, see “Column and Table Constraints” in SQL Data Types and Literals 
To increase the size of a BLOB, CLOB, or XML column, use this syntax:

  ADD  column_name  data type column attributes 

You cannot decrease the size of a column.

You cannot use ALTER TABLE to change the size, character set, or storage format of a column defined with the JSON data type.

column attributes

To modify the attributes of an existing column, use this syntax:

ADD  column_name  column attributes

INTO column_name

Add the column set as a new column partition for table_name or to add the specified column set to the existing column partition.

You use parentheses to group columns together into the same column partition.

For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.

INTO column_name specifies a column in an existing column partition. The new columns are added to this column partition.

If you do not specify INTO column_name, the new partition contains all of the columns in the group.

You cannot use an ALTER TABLE request on a join index to add columns or column partitions.

You cannot include columns with the JSON data type in a column partition.