15.10 - Data Definition Phrases - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

A data type phrase (data_type_declaration) determines the type of data to store in a column of a table on Teradata Database. When you create a table, you must specify a data type phrase for each column. A column does not have a default data type.

You can also use data type phrases to specify data conversions and casts in expressions.

Use the data type phrases in the following table to define the data type as byte, numeric, DateTime, interval, character, period, or user-defined type (UDT).

 

Data Type

ANSI SQL

Teradata Extension to ANSI SQL

Byte

 

BLOB[(n)]

X

 

BYTE[(n)]

 

X

VARBYTE[(n)]

 

X

Numeric

 

BIGINT

X

 

BYTEINT

 

X

DATEa

 

X

DECIMAL [(n[,m])]

X

 

DOUBLE PRECISION

X

 

FLOAT

X

 

INTEGER

X

 

NUMBER(n[,m])

 

X

NUMBER[(*[,m])]

 

X

NUMERIC [(n[,m])]

X

 

REAL

X

 

SMALLINT

X

 

DateTime

 

DATE

X

 

TIME [(n)]

X

 

TIMESTAMP [(n)]

X

 

Interval

 

INTERVAL

X

 

INTERVAL DAY [(n)]

X

 

INTERVAL DAY [(n)] TO HOUR

X

 

INTERVAL DAY [(n)] TO MINUTE

X

 

INTERVAL DAY [(n)] TO SECOND

X

 

INTERVAL HOUR [(n)]

X

 

INTERVAL HOUR [(n)] TO MINUTE

X

 

INTERVAL HOUR [(n)] TO SECOND

X

 

INTERVAL MINUTE [(n)]

X

 

INTERVAL MINUTE [(n)] TO SECOND [(m)]

X

 

INTERVAL MONTH

X

 

INTERVAL SECOND [(n,[m])]

X

 

INTERVAL YEAR [(n)]

X

 

INTERVAL YEAR [(n)] TO MONTH

X

 

Character

 

CHAR[(n)]

X

 

CHARACTER(n) CHARACTER SET GRAPHIC

 

X

CLOB

X

 

CHAR VARYING(n)

X

 

LONG VARCHAR

 

X

LONG VARCHAR CHARACTER SET GRAPHIC

 

X

VARCHAR(n)

X

 

VARCHAR(n) CHARACTER SET GRAPHIC

 

X

Period

 

PERIOD(DATE)

 

X

PERIOD(TIME [(n)])

 

X

PERIOD(TIMESTAMP [(n)])

 

X

UDT

 

udt_name

X

 

Note:  

  • DATE is supported both in its Teradata form and in the preferred ANSI DateTime form. For new development, define DATE using ANSI DATE type.
  • The CREATE TYPE statement determines the name of a UDT.
  • For details on the different level of ANSI compliance, see SQL Fundamentals.

    The following table lists the main Teradata SQL data type attributes.

     

    Data Type Attribute

    ANSI

    Teradata Extension to ANSI

    NOT NULL

    X

     

    UPPERCASE

     

    X

    [NOT] CASESPECIFIC

     

    X

    FORMAT format_string

     

    X

    TITLE char_string

     

    X

    AS name

    X

     

    NAMED name

     

    X

    DEFAULT value

    X

     

    DEFAULT USER

     

    X

    DEFAULT DATE

     

    X

    DEFAULT TIME

     

    X

    DEFAULT NULL

    X

     

    WITH DEFAULT

     

    X

    WITH TIME ZONE

    X

     

    CHARACTER SET

    X

     

    The expression “data type attributes” is non-ANSI, as are attributes such as TITLE and FORMAT.

    Restrictions apply to some attributes. For example, you cannot use the WITH DEFAULT attribute with UDT data types. For further details, see:

  • Chapter 11: “Default Value Control Phrases”
  • Chapter 12: “Data Type Formats and Format Phrases”
  • SQL Data Definition Language
  • The following table lists Teradata SQL storage and constraint attributes.

     

    Attribute Family

    Data Type Attribute

    ANSI

    Teradata Extension to ANSI

    Column Storage

    COMPRESS

     

    X

    COMPRESS NULL

     

    X

    COMPRESS constant_value

     

    X

    COMPRESS (multivalue_list)

     

    X

    COMPRESS USING compress_udf DECOMPRESS USING decompress_udf

     

    X

    Column Constraints

    CONSTRAINT

    X

     

    CONSTRAINT UNIQUE

    X

     

    CONSTRAINT PRIMARY KEY

    X

     

    CONSTRAINT CHECK (boolean_condition)

    X

     

    CONSTRAINT REFERENCES table_name column_name

    X

     

    Table Constraints

    FOREIGN KEY (column_name_list)

    X

     

    PRIMARY KEY

    X

     

    UNIQUE

    X

     

    CHECK (boolean_expression)

    X

     

    REFERENCES table_name
    [(column_name_list)]

    X

     

    Restrictions apply to some attributes such as COMPRESS. For further details, see:

  • “COMPRESS and DECOMPRESS Phrases” on page 22.
  • SQL Data Definition Language
  • When defining a view, any expression in the SELECT expression list can include a data definition. The data type attributes determine the form of the view display. The data type attributes for a view column can differ from those defined for the associated column of the underlying base tables or views. However, not all data definitions are relevant to view expressions.

    If you define data type attributes for a view column, these attributes will override any data type attributes defined for the associated column of the underlying base tables or views. For example, a TITLE phrase in the CREATE VIEW statement will override a TITLE phrase in the CREATE TABLE statement of the underlying table.

    Any data type attributes defined for a column in the underlying base tables or views are carried over to the view only if the associated view column is not modified in any way.

    In this example, the title “This is the title” defined in table tb is automatically carried over to the view since there were no changes to column b in the view.

    CREATE TABLE tb
    (    a INTEGER,
         b INTEGER TITLE 'This is the title');
     
    CREATE VIEW vtb AS
    (SELECT a, b
     FROM tb);
     
    SELECT TITLE (vtb.b);

    The SELECT statement produces the following output:

    Title(This is the title)
    ---------------------------
    This is the title

    In this example, the vtb2 view is created from table tb in example 1. The view definition changes the name of column b to c. Therefore, the title “This is the title” defined in table tb is not carried over to the view.

    CREATE VIEW vtb2 AS
    (SELECT a, b AS c
     FROM tb);
     
    SELECT TITLE(vtb2.c);

    The SELECT statement produces the following output:

    Title(c)
    -----------
    c

    In this example, the vtb3 view is created from table tb in example 1. The title “This is the title” defined in table tb is not carried over to the view because column b is an expression in the SELECT list of the view definition.

     
    CREATE VIEW vtb3 AS
    (SELECT a, b (SMALLINT)
     FROM tb);
     
    SELECT TITLE(vtb3.b);

    The SELECT statement produces the following output:

    Title(b)
    -----------
    b