Example: Adding a Column to a Single-Column Partition - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

This example adds a column into the single-column partition for o_orderstatus, modifying the partition to be a multicolumn partition with system-determined COLUMN format and autocompression.

     ALTER TABLE orders 
     ADD o_ordersubstatus CHAR(1) CASESPECIFIC INTO o_orderstatus;

The following equivalent request performs the same actions to alter the orders table.

     ALTER TABLE orders 
     ADD (o_ordersubstatus CHAR(1) CASESPECIFIC) INTO o_orderstatus;

Both requests alter the orders table the have the following definition with o_ordersubstatus 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)
     PARTITION BY (COLUMN ALL BUT ((o_orderstatus, o_ordersubstatus))
                     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);