17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

DEFAULT and INSERT

If you attempt to insert a row expression of the form DEFAULT VALUES, an error is returned if any column in the table is not defined with a DEFAULT value.

If you attempt to insert an explicit column_list in which a value is omitted, an error is returned if no DEFAULT is defined for the omitted column and if the column was declared NOT NULL.

DEFAULT and Period Data Types

The following data type attributes are supported for a Period column:
  • DEFAULT NULL
  • DEFAULT value

    The specified value must be either a Period literal or a Period value constructor.

    When a Period value constructor is used for specifying the default value, the following rules apply:
    • A Period value constructor with single argument must not be used as a default value; otherwise, an error is reported.
    • The beginning bound must be specified using a DateTime literal, DATE, CURRENT_DATE, or CURRENT_TIMESTAMP[(n)]; otherwise, an error is reported.
    • If the beginning bound is a DateTime literal, the ending bound must be specified using a DateTime literal or, if the beginning bound has a DATE or TIMESTAMP data type, UNTIL_CHANGED. Otherwise, an error is reported.
    • If the beginning bound is DATE, CURRENT_DATE, or CURRENT_TIMESTAMP[(n)], the ending bound must be UNTIL_CHANGED or a DateTime literal specifying the equivalent value of UNTIL_CHANGED; otherwise, an error is reported.
The following data type attributes are not supported for a Period column:
  • DEFAULT USER
  • DEFAULT DATE
  • DEFAULT TIME
  • DEFAULT CURRENT_DATE
  • DEFAULT CURRENT_TIME[(n)]
  • DEFAULT CURRENT_TIMESTAMP[(n)]

Column Data Types and DEFAULT Values

A default value must be compatible with the data type specified for the column it is inserted into. For example, the phrase INTEGER DEFAULT 3.5 is not valid and returns an error.

For DateTime data types, SQL Engine performs an implicit conversion if the default value specified in a CREATE/ALTER TABLE statement differs from the data type of the column. For example, the following statement is valid:

CREATE TABLE tab1 (F1 INT, F2 TIMESTAMP(0) DEFAULT CURRENT_DATE);

The following table lists the default values you can specify for each of the column types.

Column Data Type Supported Default Values
DATE CURRENT_DATE
CURRENT_TIMESTAMP
DATE literal
TIMESTAMP literal
TIME CURRENT_TIME
CURRENT_TIMESTAMP
TIME literal
TIMESTAMP literal
TIMESTAMP CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DATE literal
TIME literal
TIMESTAMP literal
In addition, Vantage also supports DEFAULT phrase specifications such as the following:
  • CREATE TABLE tab2 (i INT, j INTERVAL DAY DEFAULT 4);
  • CREATE TABLE tab3 (i INT, j INTERVAL HOUR TO MINUTE DEFAULT '11:23' );

For more information about implicit conversions of DateTime data types, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

The default value (excepting keywords) for character columns must be in the repertoire of the character data type defined for the column and session character set.

In an ALTER TABLE statement, the DEFAULT phrase can be used with a keyword to override a previously-defined value.

Unsupported Data Types

You cannot specify the DEFAULT phrase for columns defined with the following data types:
  • BLOB or CLOB
  • UDT, with the exception of DEFAULT NULL

DEFAULT Values and CHECK Constraints

A default value must not violate any CHECK constraints specified for the column. If a default value would violate a CHECK constraint, the conflict is not recognized at the time the table is defined or altered. The conflict is recognized the first time that an INSERT or UPDATE attempts to enter a NULL, which would be replaced by the default.

For character data, constraints are checked using the current session collation. Therefore, it is possible for a default value to meet the constraint for one collation and violate the constraint for another collation.

DEFAULT Values and Identity Columns

You cannot specify the DEFAULT attribute for Identity columns.

DEFAULT Values and Built-In Functions

A keyword used as a constant_value inserts a string that is already known to the system, such as the system date, the system time, or the name of the user defining the column.

Use of the Teradata SQL built-in functions DATE and TIME as default values is deprecated. They are non-ANSI standard and continue in the language only to maintain backward compatibility. Use the ANSI-standard CURRENT_DATE and CURRENT_TIME instead.

Maximum Length for a DEFAULT Value

The maximum length of constant_value is 510 characters for character columns, and 510 bytes for byte columns.

System Values for DEFAULT Phrase

Unlike the Teradata WITH DEFAULT phrase, there are no system default values for the DEFAULT phrase.

Values for DEFAULT Phrase Keywords

The following table lists the DEFAULT phrase forms, the function performed by each variable or keyword, and the corresponding default data types.

This form … Inserts … Using this data type …
DEFAULT constant_value the value defined as the default for the column the type defined for the table column.
DEFAULT DATE quotestring the date value specified by quotestring as the default for the column DATE.
DEFAULT NULL a NULL None.
DEFAULT CURRENT_DATE the current system date DATE.
DEFAULT CURRENT_TIME the current system time TIME.
DEFAULT CURRENT_TIMESTAMP the current system date and time TIMESTAMP.
DEFAULT USER

Teradata extension to the ANSI standard

the user name of the current user CHAR(n) CHARACTER SET UNICODE

VARCHAR(n) CHARACTER SET UNICODE

where n is the length of the longest permissible user name.

If DEFAULT NULL is specified with the NOT NULL phrase in CREATE TABLE or ALTER TABLE ADD statements, no error or warning messages are returned for the statements. However, an error occurs the first time an INSERT or UPDATE attempts to enter a null.

You can assign a literal value, such as a blank, as the DEFAULT for a column as follows:

CHAR(1) DEFAULT ' ' ...