CREATE TABLE [AS] SQL Statement | VantageCloud Lake - CREATE TABLE and CREATE TABLE AS - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

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, copies data and statistics to the new table.

A table stored in persistent storage in the Block File System (which is accessible only on the primary cluster) can use any table-level attributes. A table stored in the Object File System (which can be accessed from the primary cluster or the compute cluster) does not follow the same conventions as a table stored on the Block File System. Object File System tables have default settings for the following attributes, which you cannot change:
  • BLOCKCOMPRESSION
  • CHECKSUM
  • DATABLOCKSIZE
  • FREESPACE
  • MERGEBLOCKRATIO

The NO FALLBACK attribute is ignored.

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.

CREATE TABLE and CREATE TABLE AS do not apply to the following kinds of tables. To create those tables, see the following topics.

Table Kind  
Object File System CREATE TABLE and CREATE TABLE AS (Object File System Table Form)
Queue CREATE TABLE (Queue Table Form)
Global temporary trace CREATE GLOBAL TEMPORARY TRACE TABLE
Foreign CREATE FOREIGN TABLE
Error CREATE ERROR TABLE

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

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

CREATE TABLE supports the following clauses:
Also see the following sections:

ANSI Compliance

CREATE TABLE is ANSI SQL:2011-compliant with extensions.

Global temporary tables are ANSI SQL:2011-compliant.

Other SQL dialects support similar non-ANSI standard statements with names such as the following:
  • DECLARE GLOBAL TEMPORARY TABLE
  • CREATE TEMP TABLE
Volatile tables are a Teradata extension to the ANSI SQL:2011 standard.
Volatile tables are only supported on the Block File System on the primary cluster. They are not available for the Object File System.
Other SQL dialects support similar non-ANSI standard statements with names such as the following:
  • DECLARE GLOBAL TEMPORARY TABLE

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.

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

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 has 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
See: