15.10 - DEFAULT Phrase - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

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.

where:

 

Syntax element …

Specifies …

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 details on built-in functions, see SQL Functions, Operators, Expressions, and Predicates.

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
  •  

    DEFAULT is ANSI SQL:2011 compliant.

    Also see the non-ANSI “WITH DEFAULT Phrase” on page 278.

    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.

    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)]
  • 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 details about implicit conversions of DateTime data types, see “Data Type Conversions” in SQL Functions, Operators, Expressions, and Predicates.

    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.

    You cannot specify the DEFAULT phrase for columns defined with the following data types:

  • BLOB or CLOB
  • UDT, with the exception of DEFAULT NULL
  • 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.

    You cannot specify the DEFAULT attribute for Identity columns.

    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.

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

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

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

    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.

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

       F4 DATE DEFAULT DATE '2000-01-01' 

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

       Stage TIME(3) DEFAULT CURRENT­_TIME(3)

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

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