15.00 - Columns - Teradata Database

Teradata Database SQL Fundamentals

Teradata Database
Programming Reference


A column is a structural component of a table and has a name and a declared type. Each row in a table has exactly one value for each column. Each value in a row is a value in the declared type of the column. The declared type includes nulls and values of the declared type.

Defining Columns

The column definition clause of the CREATE TABLE statement defines the table column elements.

A name and a data type must be specified for each column defined for a table.

Here is an example that creates a table called employee with three columns:

   CREATE TABLE employee
     (deptno INTEGER
     ,name CHARACTER(23)
     ,hiredate DATE);

Each column can be further defined with one or more optional attribute definitions. The following attributes are also elements of the SQL column definition clause:

  • Data type attribute declaration, such as NOT NULL, FORMAT, TITLE, and CHARACTER SET
  • COMPRESS column storage attributes clause
  • DEFAULT and WITH DEFAULT default value control clauses
  • PRIMARY KEY, UNIQUE, REFERENCES, and CHECK column constraint attributes clauses
  • Here is an example that defines attributes for the columns in the employee table:

       CREATE TABLE employee
         (deptno INTEGER NOT NULL
         ,hiredate DATE DEFAULT CURRENT_DATE);

    System-Derived and System-Generated Columns

    In addition to the table columns that you define, tables contain columns that Teradata Database generates or derives dynamically.





    A column that was specified with the GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY option in the table definition.

    Object Identifier (OID)

    For a table that has LOB columns, OID columns store pointers to subtables that store the actual LOB data.


    For a table that is defined with a partitioned primary index (PPI), the PARTITION column provides the partition number of the combined partitioning expression associated with a row, where the combined partitioning expression is derived from the partitioning expressions defined for each level of the PPI. This is zero for a table that does not have a PPI.

    For more information on PPIs, see “Partitioned and Nonpartitioned Primary Indexes” on page 31.


    For tables that are defined with a multilevel PPI, these columns provide the partition number associated with the corresponding level. These are zero for a table that does not have a PPI and zero if the level is greater than the number of partitions.


    Contains the row identifier value that uniquely identifies the row.

    For more information on row identifiers, see “Row Hash and RowID” on page 28.

    Restrictions apply to using the system-derived and system-generated columns in SQL statements. For example, you can use the keywords PARTITION and PARTITION#L1 through PARTITION#L15 in a query where a table column can be referenced, but you can only use the keyword ROWID in a CREATE JOIN INDEX statement.

    Related Topics


    For more information on …

    See …

    data types

    “Data Types” on page 24.

    CREATE TABLE and the column definition clause

    SQL Data Definition Language.

    system-derived and system-generated columns

    Database Design.