16.20 - column_name - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

Specifies the name of one or more non-QITS columns, in the order they are to be defined for the table.

For information about naming database objects, see Teradata Vantage™ SQL Fundamentals, B035-1141.

You can define up to 2,048 columns, including the mandatory QITS column, for a queue table.

data type
You must specify a single data type for each column_name.
Queue tables cannot contain columns with BLOB or CLOB data types.
For information on data types, data type attributes, and converting between data types, see Teradata Vantage™ Data Types and Literals, B035-1143.
If you do not specify explicit formatting, a column assumes the default format for the data type, which can be specified by a custom data formatting specification (SDF) defined by the tdlocaledef utility. See Teradata Vantage™ - Database Utilities , B035-1102 . Explicit formatting applies to the parsing and retrieval of character strings.
column attributes
One or more data definition phrases that define data for the column.
You cannot specify a character server data set of KANJI1. Otherwise, Teradata Database returns an error to the requestor.
Column data attribute specifications are optional. If you specify attributes for a column, you should define its data type prior to defining the attributes.

Example: Using SELECT AND CONSUME on Queue Tables with JSON, ST_GEOMETRY, or XML Columns

You can create queue tables with inline JSON, ST_GEOMETRY, or XML Columns, for example, JSON(1000). However, to retrieve data from this table using SELECT AND CONSUME, you must use non-LOB transforms.

Below is the table definition for this example.

CREATE SET TABLE qt1, QUEUE
(
      QITS TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
      col1 INTEGER,
      jsn JSON(64000) CHARACTER SET LATIN
)
PRIMARY INDEX (col1);
Then, we insert a row of data.
INSERT INTO qt1 VALUES (current_timestamp, 1, '{"a":123}');

Then, we create a user with non-LOB transforms for JSON, ST_GEOMETRY, and XML data.

CREATE USER User1 AS PERM=1e8 * (HASHAMP () + 1), PASSWORD=secret,
    TRANSFORM ( JSON CHARACTER SET LATIN=TD_JSON_VARCHAR,
                ST_GEOMETRY=TD_GEO_VARCHAR,
                XML=TD_XML_VARCHAR);

Now, log in as User1 to retrieve the data.

SELECT AND CONSUME TOP 1 col1, jsn FROM qt1;

 *** Query completed. One row found. 2 columns returned.
 *** Total elapsed time was 1 second.
       col1 jsn
----------- ---------------------------------------------------------------
          1 {"a":123}