15.10 - ADD column_name - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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 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 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 SQL 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 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.

Example: Adding or Modifying NUMBER Columns

Assume you create the following table.

     CREATE TABLE num_tab (
       n1 NUMBER(*,3),
       n2 NUMBER,
       n3 NUMBER(*),
       n4 NUMBER(5,1));

This example modifies the precision of an existing NUMBER column in num_tab.

     ALTER TABLE num_tab ADD n4 NUMBER(9,1);

This example modifies both the precision and the scale of an existing NUMBER column in the original definition of num_tab. The example is not valid if submitted for the revised definition of num_tab as it would be after Teradata Database processed the previous example where the precision of column n4 was altered.

     ALTER TABLE num_tab ADD n4 NUMBER(10,6);

This example adds a NUMBER column to num_tab.

     ALTER TABLE num_tab ADD n6 NUMBER(12,4);

Example: Renaming a Column and Then Adding a New Column Using the Previous Name of the Renamed Column

You can rename an existing column, then create a new column, perhaps with a different data type, using the old name of the renamed column. For example, suppose you have the following table definition:

     CREATE SET TABLE t1, NO FALLBACK, NO BEFORE JOURNAL, 
                          NO AFTER JOURNAL,
       upi INTEGER NOT NULL,
       f1  FLOAT,
       d1  DECIMAL(7,2),
       i1  INTEGER)
     UNIQUE PRIMARY INDEX(upi);

Rename column f1 as f2 and then add a new column named f1 having the INTEGER data type rather than the FLOAT data type previously associated with the column now named f2:

     ALTER TABLE t1 
       RENAME f1 AS f2,
       ADD    f1 INTEGER;
     *** Table has been modified. 
     *** Total elapsed time was 1 second.

Rename column i1 as i2 and then add a new column named i1 having the DECIMAL data type rather than the INTEGER data type previously associated with the column now named i2:

     ALTER TABLE t1 
      RENAME i1 AS i2,
      ADD    i1 DECIMAL(8,3);
     *** Table has been modified. 
     *** Total elapsed time was 1 second.

Display the new table definition using the SHOW TABLE statement. See SHOW object.

     SHOW TABLE t1;
      *** Text of DDL statement returned. 
       *** Total elapsed time was 1 second.
     CREATE SET TABLE user_name.t1, NO FALLBACK,NO BEFORE JOURNAL,
                              NO AFTER JOURNAL,CHECKSUM = DEFAULT (
       upi INTEGER NOT NULL,
       f2  FLOAT,
       d1  DECIMAL(7,2),
       i2  INTEGER,
       f1  INTEGER,
       i1  DECIMAL(8,3)
     UNIQUE PRIMARY INDEX (upi);

Example: Adding a LOB Column to a Table

The following example adds the CLOB column extended_description to a table named partshistory:

    ALTER TABLE partshistory
    ADD extended_description CLOB;

Example: Adding Columns to a Column Partition

This example adds two columns into the single-column partition for o_comment and modifies the partition to be a multicolumn partition with system-determined COLUMN format and autocompression.

Any of the following equivalent requests perform the same actions to alter the orders table.

     ALTER TABLE orders 
     ADD (o_comment_ext1 VARCHAR(79), o_comment_ext2 VARCHAR(79))
     INTO o_comment;
     ALTER TABLE orders 
     ADD o_comment_ext1 VARCHAR(79) INTO o_comment,
     ADD o_comment_ext2 VARCHAR(79) INTO o_comment;
     ALTER TABLE orders 
     ADD (o_comment_ext1 VARCHAR(79)) INTO o_comment,
     ADD (o_comment_ext2 VARCHAR(79)) INTO o_comment;
     ALTER TABLE orders 
     ADD (o_comment_ext1 VARCHAR(79)) INTO o_comment,
     ADD o_comment_ext2 VARCHAR(79) INTO o_comment;
     ALTER TABLE orders 
     ADD o_comment_ext1 VARCHAR(79) INTO o_comment,
     ADD (o_comment_ext2 VARCHAR(79)) INTO o_comment;

These all produce the following table definition with the new columns set to NULL in each row of the table.

     CREATE TABLE orders (
       o_orderkey       INTEGER NOT NULL,
       o_custkey        INTEGER,
       o_orderstatus    CHARACTER(1) CASESPECIFIC,
       o_totalprice     DECIMAL(13,2) NOT NULL,
       o_ordertsz       TIMESTAMP(6) WITH TIME ZONE NOT NULL,
       o_comment        VARCHAR(79),
       o_salesperson    VARCHAR(5),
       o_ordersubstatus CHARACTER(1) CASESPECIFIC,
       o_comment_ext1   VARCHAR(79),
       o_comment_ext2   VARCHAR(79) )
     PARTITION BY (COLUMN 
                   ALL BUT ((o_orderstatus, o_ordersubstatus),
                           (o_comment, o_comment_ext1, o_comment_ext2))
                           ADD 4,
                   RANGE_N(o_ordertsz 
                   BETWEEN TIMESTAMP '2003-01-01 00:00:00.000000+00:00'
                   AND     TIMESTAMP '2009-12-31 23:59:59.999999+00:00' 
                   EACH INTERVAL '1' MONTH) ),
     UNIQUE INDEX(o_orderkey);

Example: Adding a Column Partition to a Table with a Primary AMP Index

Assume the following table definition, with autocompression as the default.

CREATE TABLE Orders
    ( o_orderkey INTEGER NOT NULL,
      o_custkey INTEGER,
      o_orderstatus CHAR(1) CASESPECIFIC,
      o_totalprice DECIMAL(13,2) NOT NULL,
      o_ordertsz TIMESTAMP(6) WITH TIME ZONE NOT NULL,
      o_comment VARCHAR(79) )
  PRIMARY AMP INDEX (o_orderkey), PARTITION BY COLUMN,
  UNIQUE INDEX (o_orderkey);

The following ALTER TABLE statement adds a single-column partition, with system-determined COLUMN format and autocompression:

ALTER TABLE Orders ADD o_salesperson VARCHAR(5);

This results in a table definition as follows:

CREATE TABLE Orders
    ( o_orderkey INTEGER NOT NULL,
      o_custkey INTEGER,
      o_orderstatus CHAR(1) CASESPECIFIC,
      o_totalprice DECIMAL(13,2) NOT NULL,
      o_ordertsz TIMESTAMP(6) WITH TIME ZONE NOT NULL,
      o_comment VARCHAR(79),
      o_salesperson VARCHAR(5) )
  PRIMARY AMP INDEX (o_orderkey) PARTITION BY COLUMN AUTO COMPRESS,
  UNIQUE INDEX (o_orderkey);

Example: Adding Another Column Partition to a Table with a Primary AMP Index

The following ALTER TABLE statement adds another single-column partition, with system-determined COLUMN format and no autocompression as the default:

ALTER TABLE Orders ADD o_ordersubstatus CHAR(1) CASESPECIFIC;

This results in a table definition as follows:

CREATE TABLE Orders
    ( o_orderkey INTEGER NOT NULL,
      o_custkey INTEGER,
      o_orderstatus CHAR(1) CASESPECIFIC,
      o_totalprice DECIMAL(13,2) NOT NULL,
      o_ordertsz TIMESTAMP(6) WITH TIME ZONE NOT NULL,
      o_comment VARCHAR(79),
      o_salesperson VARCHAR(5),
      o_ordersubstatus CHAR(1) CASESPECIFIC )
  PRIMARY AMP INDEX (o_orderkey)
  PARTITION BY
    COLUMN AUTO COMPRESS ALL BUT (o_ordersubstatus NO AUTO COMPRESS),
  UNIQUE INDEX (o_orderkey);

Example: Adding a Two-Column Partition to a Table with a Primary AMP Index

The following ALTER TABLE statement adds a two-column partition, with system-determined COLUMN format and no autocompression as the default:

ALTER TABLE Orders ADD (o_comment_ext1 VARCHAR(79), o_comment_ext2 VARCHAR(79));

This results in a table definition as follows:

CREATE TABLE Orders
    ( o_orderkey INTEGER NOT NULL,
      o_custkey INTEGER,
      o_orderstatus CHAR(1) CASESPECIFIC,
      o_totalprice DECIMAL(13,2) NOT NULL,
      o_ordertsz TIMESTAMP(6) WITH TIME ZONE NOT NULL,
      o_comment VARCHAR(79),
      o_salesperson VARCHAR(5),
      o_ordersubstatus CHAR(1) CASESPECIFIC,
      o_comment_ext1 VARCHAR(79),
      o_comment_ext2 VARCHAR(79) )
  PRIMARY AMP INDEX (o_orderkey)
  PARTITION BY
    COLUMN AUTO COMPRESS ALL BUT (
                          o_ordersubstatus NO AUTO COMPRESS,
                          (o_comment_ext1, o_comment_ext2) NO AUTO COMPRESS ),
  UNIQUE INDEX(o_orderkey);