Procedure to Change Column Data Types - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

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