16.20 - CREATE TABLE - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1144-162K

Purpose

Defines the column names, column data types and attributes, primary and secondary indexes, column- and table-constraints, partitioning, and other attributes of a new table. The CREATE TABLE AS form copies column definitions to a new table. Optionally, data and statistics are copied to the new table.

The table structure definition is stored in the data dictionary for all table types, except volatile tables. While processing a CREATE TABLE statement, an EXCLUSIVE lock is placed on the table being created.

For information about global temporary trace tables, see CREATE GLOBAL TEMPORARY TRACE TABLE.

To create queue tables, see CREATE TABLE (Queue Table Form).

To log batch insert and update errors, you must create an error table for each data table for which you want to track errors. See CREATE ERROR TABLE.

For information about temporal tables and temporal syntax, see Teradata Vantage™ ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ Temporal Table Support , B035-1182 .

For information on creating time series tables with a Primary Time Index (PTI), see Teradata Vantage™ Time Series Tables and Operations, B035-1208.

Required Privileges

No privileges are required to create, access, modify, or drop volatile tables. For all other table types, you must have the CREATE TABLE privilege on the database or user in which the table is created.

To specify the MAP option, you must have been granted the specified map, except when you specify the same map determined to be the default map according to the following order of precedence:
  • If the immediate owner is not the creator:
    • Default map, if defined, for the profile of the immediate owner.
    • Default map, if defined, for the immediate owner.
    • System-default map.
  • Default map, if defined, for the profile of the creator.
  • Default map, if defined, for the creator.
  • System-default map.

See GRANT MAP in Teradata Vantage™ SQL Data Control Language, B035-1149. Also, see the CREATE PROFILE DEFAULT MAP option, the CREATE USER DEFAULT MAP option, and the CREATE DATABASE DEFAULT MAP option.

If you specify the JOURNAL option, then you must also have INSERT privilege on the journal table.

To access a table that contains UDT columns, you must have at least one of the following privileges:
  • UDTUSAGE on the specified UDT
  • UDTUSAGE on the SYSUDTLIB database
  • UDTTYPE on the SYSUDTLIB database
  • UDTMETHOD on the SYSUDTLIB database

You must have the CONSTRAINT ASSIGNMENT privilege to create a table that includes one or more row-level security columns.

Privileges Granted Automatically

No privileges are granted on newly created volatile tables, because no privileges are needed to access the table.

For other table types, the creator receives all of the following privileges WITH GRANT OPTION on the newly created table:
  • CREATE TRIGGER
  • DELETE
  • DROP TABLE
  • DROP TRIGGER
  • DUMP
  • INDEX
  • INSERT
  • REFERENCES
  • RESTORE
  • SELECT
  • STATISTICS
  • UPDATE

Syntax - CREATE TABLE

































Syntax - CREATE TABLE … AS