ADD COLUMN (column_name)

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 set as a new column partition. 
For information about naming database objects, see SQL Fundamentals, B035-1141.
The table must be column-partitioned and the columns being added cannot already exist. You cannot alter a join index to add columns or column partitions using an ALTER TABLE request. Specifies column storage format for the column_name or column_name group. Alters the column partition containing the column to have COLUMN format, if it is not already defined with COLUMN format. If you do not specify COLUMN, ROW, or SYSTEM format and the column partition containing the column:
  • Does not have a system-determined format, the column partition is not altered and the format remains as most recently defined.
  • Has a system-determined format, the format remains system-defined.

    If the altered column partition containing the column has a system-determined column partition format, the column partition is altered to a newly appropriate format if it does not already have that format.

data type
Data type for the column.
column attributes
Specify attributes for the column.
AUTO COMPRESS
Autocompress data in the column.
NO AUTO COMPRESS
Do not autocompress data in the column.

Example: Adding a Two-Column Partition to a Table

This example uses a table definition with more columns than the version of orders used in Example: Adding a Single-Column Partition to a Table. The system default is AUTO COMPRESS. The ALTER TABLE statements all add the same two-column partition, (o_ship_addr, o_bill_addr ) to the orders 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 AUTO COMPRESS ALL BUT (o_ordersubstatus
                                                NO AUTO COMPRESS,
                                               (o_comment_ext1,
                                                o_comment_ext2)
                                                NO AUTO COMPRESS),
     UNIQUE INDEX(o_orderkey);

The following ALTER TABLE requests all add the same two-column partition (o_ship_addr , o_bill_addr ) to the table with user-specified ROW format and autocompression.

     ALTER TABLE orders 
       ADD ROW(o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200)) 
       AUTO COMPRESS;
     ALTER TABLE orders 
       ADD ROW(o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200));
     ALTER TABLE orders 
       ADD (o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200));
     ALTER TABLE orders 
       ADD (o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200)) 
       AUTO COMPRESS;
     ALTER TABLE orders 
       ADD (o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200)) 
       NO AUTO COMPRESS;
     ALTER TABLE orders 
       ADD ROW(o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200)) 
       NO AUTO COMPRESS;
     ALTER TABLE orders 
       ADD o_bill_addr VARCHAR(200) INTO o_ship_addr,
       ADD ROW(o_ship_addr);

The resulting table has the following definition.

     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),
       o_ship_addr      VARCHAR(500),
       o_bill_addr      VARCHAR(200) )
     PARTITION BY COLUMN AUTO COMPRESS ALL BUT (o_ordersubstatus
                                                 NO AUTO COMPRESS,
                           (o_comment_ext1, 
                                                 o_comment_ext2) 
                                                 NO AUTO COMPRESS,
                   ROW(o_ship_addr, o_bill_addr)),
     UNIQUE INDEX(o_orderkey);

Example: Adding a Two-Column and a Single-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 two-column partition and a single-column partition to the table, with user-specified COLUMN format for the first partition, system-determined COLUMN format for the second partition, and autocompression by default.

ALTER TABLE Orders 
  ADD COLUMN(o_alt_ship_addr VARCHAR(500), o_alt_bill_addr VARCHAR(200)),
      ADD o_item_count INTEGER;

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),
      o_ship_addr VARCHAR(500),
      o_bill_addr VARCHAR(200),
      o_alt_ship_addr VARCHAR(500),
      o_alt_bill_addr VARCHAR(200),
      o_item_count INTEGER )
  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,
                     ROW(o_ship_addr, o_bill_addr) NO AUTO COMPRESS,
                     COLUMN(o_alt_ship_addr, o_alt_bill_addr) ),
  UNIQUE INDEX (o_orderkey);