DEFAULT Phrase Usage Notes - 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

DEFAULT and INSERT

If you try 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 try 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 for a column must be compatible with the data type specified for the column. For example, the phrase INTEGER DEFAULT 3.5 is not valid and returns an error.

For DateTime data types, Analytics Database 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 Implicit Conversion of DateTime Types.

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

DEFAULT Values and CHECK Constraints

A default value must not violate any CHECK constraints specified for the column. If a default value violates a CHECK constraint, the conflict is not recognized when the table is defined or altered. The conflict is recognized the first time an INSERT or UPDATE tries to enter a NULL.

For character data, constraints are checked using the current session collation. Therefore, a default value may 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. DATE and TIME 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.

Form Inserted Value Data Type of Inserted Value
DEFAULT constant_value Value defined as the default for the column the type defined for the table column.
DEFAULT DATE quotestring Date value specified by quotestring as the default for the column DATE.
DEFAULT NULL NULL None.
DEFAULT CURRENT_DATE Current system date DATE.
DEFAULT CURRENT_TIME Current system time TIME.
DEFAULT CURRENT_TIMESTAMP Current system date and time TIMESTAMP.
DEFAULT USER

Teradata extension to the ANSI standard

 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 tries 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 ' ' ...