DEFAULT Phrase | Data Types and Literals | Teradata Vantage - DEFAULT Phrase - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zsn1556242031050.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantage™

Specifies that a user-defined default value is to be inserted in the field when a value is not specified for a column in an INSERT statement.

Syntax

DEFAULT {
  constant_value |
  { DATE | TIME | TIMESTAMP } quotestring |
  INTERVAL [ sign ] quotestring qualifier
}
constant_value
A default value to be inserted when a column in the target table is omitted from the column_list specification of an INSERT statement.

When no DEFAULT phrase is provided, a NULL is inserted into the field for nullable columns, otherwise an error is returned.

A default value can be a keyword or built-in function. For more information on built-in functions, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

The following defines the valid keywords and functions:
  • number – specifies to insert a numeric literal value as the default value
  • NULL – specifies to insert a null (for nullable columns) as the default value
  • USER – specifies to insert the user name of the current user as the default value
  • CURRENT_DATE – specifies to insert the current system date as the default value
  • CURRENT_TIME – specifies to insert the current system time as the default value
  • CURRENT_TIMESTAMP – specifies to insert the current system date and time as the default value
DATE
A date value in ANSI DATE literal format as the insert default.
TIME
A time value in ANSI TIME literal format as the insert default.
TIMESTAMP
A timestamp value in ANSI TIMESTAMP literal format as the insert default.
INTERVAL
An interval value in ANSI INTERVAL format as the insert default.
quotestring
A default value represented as a string enclosed in apostrophes.
sign
The sign of an interval value.
qualifier
One of the following interval time periods:
  • YEAR
  • YEAR TO MONTH
  • MONTH
  • DAY
  • DAY TO HOUR
  • DAY TO MINUTE
  • DAY TO SECOND
  • HOUR
  • HOUR TO MINUTE
  • HOUR TO SECOND
  • MINUTE
  • MINUTE TO SECOND
  • SECOND

ANSI Compliance

DEFAULT is ANSI SQL:2011 compliant.

Also see the non-ANSI WITH DEFAULT Phrase.

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, Teradata 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, Teradata Database 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 “Data Type Conversions” in 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 ' ' ...

Example: Numeric

Suppose the DeptNo column is defined in table Departments as follows.

CREATE TABLE Departments
   (DeptName CHARACTER(36)
   ,DeptNo SMALLINT DEFAULT 100 FORMAT '999' BETWEEN 100 AND 900
   ,ManagerID INTEGER);

When the value for the DeptNo field is not provided in an INSERT statement, then the value 100 with type SMALLINT is inserted automatically.

Example: Date Literal

The following example shows a DATE column specified with a DEFAULT date literal:

F4 DATE DEFAULT DATE '2000-01-01'

Example: Current Time

The following example shows a TIME column specified with a DEFAULT of the current time:

Stage TIME(3) DEFAULT CURRENT_TIME(3)

Example: Interval

The following example shows an INTERVAL column specified with a DEFAULT interval:

Scale INTERVAL YEAR(2) DEFAULT INTERVAL -'10' YEAR