17.10 - ALTER TABLE ADD column_name Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

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 Vantage 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: Modifying a Table with JSON non-LOB and LOB Columns

Following is the table definition for a table with non-LOB and LOB JASON columns.

CREATE TABLE jsonTable (id INTEGER,
               jsn1 JSON(1000) CHARACTER SET LATIN,
               jsn2 JSON(1M) INLINE LENGTH 30000 CHARACTER SET LATIN);

This statement increases the maximum length of the non-LOB column.

ALTER TABLE jsonTable ADD jsn1 JSON(2000);

This statement increases the maximum length of the LOB column.

ALTER TABLE jsonTable 
      ADD jsn2 JSON(2M) INLINE LENGTH 30000 CHARACTER SET LATIN;

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);