Procedure to Change Column Data Types - Advanced SQL Engine - Teradata Database

To make a change to a column data type that affects existing column data, use the following procedure.

1. Create a new table with a different name that contains the changed data type attributes.
2. Populate the new table using an INSERT … SELECT request.
3. Catalog the privileges of the old table before step 4. Use the following syntax.

       SELECT username, accessright, grantauthority, columnname,
              allnessflag
       FROM dbc.allrightsV
       WHERE tablename = 'table_name'
       AND   databasename = 'database_name';

4. Drop the old table.
5. Rename the new table with the name of the old table.

   As an example, use the following sequence of requests to expand the data type attribute for the name column from 12 to 14 characters.

       CREATE TABLE temp, FALLBACK  (
         EmpNo INTEGER NOT NULL FORMAT ZZZZ9,
         Name  CHARACTER(14) NOT NULL,
           …
         HCap  BYTEINT FORMAT 'Z9')
       UNIQUE PRIMARY INDEX (index_name) ;

6. Once the table is created, populate it, drop the old employee table, and rename the temporary table.

   The following example shows how this is done.

       INSERT INTO temp
         SELECT *
         FROM employee;
       DROP TABLE employee;
       RENAME TABLE temp TO employee;

   A different name, temp, is used in recreating the employee table because the employee table already exists. If you submit a CREATE TABLE request for an existing table, you receive an error.

   To facilitate recreating a table according to Step 1, you can display the CREATE TABLE DDL text for the table by submitting a SHOW TABLE request.

   For example, the following request displays a reconstruction of the CREATE TABLE DDL text for table dept_em.

       SHOW TABLE dept_em;

   If you are using BTEQ, you can change the SQL text (that is, change the table name and index) using BTEQ edit commands and submit the new table definition using the BTEQ SUBMIT command.

7. Grant the same privileges to the employee table again, as they were lost when the table was dropped. Use the same privileges cataloged in step 3.

Increasing the length of VARCHAR or VARBYTE does not change the current format. Instead, you should consider including a new FORMAT phrase in the request.

ALTER TABLE provides many extensions not offered by ANSI SQL. For example, changing column attributes in ANSI SQL is restricted to setting or dropping a default clause, which is not the case for SQL in the Teradata Database.