15.10 - WITH 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 system-defined default value is to be inserted in the field when a value is not specified for a column in an INSERT statement.

WITH DEFAULT is a Teradata extension to the ANSI SQL:2011 standard.

The ANSI form of this phrase is documented in “DEFAULT Phrase” on page 272.

If you use a WITH DEFAULT phrase with a numeric range constraint, the range must include the system default value assigned to the data type defined for the column.

When a CREATE TABLE statement is processed, all WITH DEFAULT phrases are converted to DEFAULT phrases in which the system default value becomes the default constant_value.

The WITH DEFAULT phrase is mutually exclusive with the DEFAULT phrase.

The value of a system default is determined by the data type defined for the column. The data types and associated system default values appear in the following table.

 

Data Type

System Default

BIGINT

Zero

BYTE[(n)]

Zero if n omitted, or n binary zeros

BYTEINT

Zero

CHAR[(n)]

Depends on the server character set as follows:

  • If LATIN, the system default is ASCII SPACE (0x20).
  • If UNICODE, the system default is SPACE (U+0020).
  • If KANJISJIS or KANJI1, the system default is ASCII SPACE (0x20)
  • If GRAPHIC, the system default is IDEOGRAPHIC SPACE (U+3000)
  • DATE

    Current date

    DECIMAL NUMERIC

    Zero

    DOUBLE PRECISION

    Zero

    FLOAT

    Zero

    INTEGER

    Zero

    INTERVAL DAY

    INTERVAL ' 0 ' DAY

    INTERVAL DAY TO HOUR

    INTERVAL ' 0 00 ' DAY TO HOUR

    INTERVAL DAY TO MINUTE

    INTERVAL ' 0 00:00 ' DAY TO MINUTE

    INTERVAL DAY TO SECOND

    INTERVAL ' 0 00:00:00 ' DAY TO SECOND

    INTERVAL HOUR

    INTERVAL ' 0 ' HOUR

    INTERVAL HOUR TO MINUTE

    INTERVAL ' 00:00 ' HOUR TO MINUTE

    INTERVAL HOUR TO SECOND

    INTERVAL ' 00:00:00 ' HOUR TO SECOND

    INTERVAL MINUTE

    INTERVAL ' 0 ' MINUTE

    INTERVAL MINUTE TO SECOND

    INTERVAL ' 00:00 ' MINUTE TO SECOND

    INTERVAL MONTH

    INTERVAL ' 0 ' MONTH

    INTERVAL SECOND

    INTERVAL ' 0 ' SECOND

    INTERVAL YEAR

    INTERVAL ' 0 ' YEAR

    INTERVAL YEAR TO MONTH

    INTERVAL ' 0-00 ' YEAR TO MONTH

    NUMBER

    Zero

    PERIOD(DATE)

    The default value is set implicitly using a Period value constructor with the beginning argument set to CURRENT_DATE and the ending argument set to UNTIL_CHANGED.

    PERIOD(TIMESTAMP)

    The default value is set implicitly using a Period value constructor with the beginning argument set to CURRENT_TIMESTAMP[(n)] and the ending argument set to UNTIL_CHANGED.

    REAL

    Zero

    SMALLINT

    Zero

    TIME

    CURRENT_TIME

    TIMESTAMP

    CURRENT_TIMESTAMP

    TIMESTAMP WITH TIME ZONE

    CURRENT_TIMESTAMP

    TIME WITH TIME ZONE

    CURRENT_TIME

    VARBYTE (n)

    ' ' (null string)

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

  • BLOB or CLOB
  • UDT
  • PERIOD(TIME)
  • If the EdLev column is defined as:

       EdLev BYTEINT FORMAT 'Z9' NOT NULL WITH DEFAULT, 

    then the column definition for EdLev is stored as:

       EdLev BYTEINT NOT NULL DEFAULT 0 FORMAT 'Z9', 

    The following INSERT statement adds a row containing a zero in the EdLev field defined in Example 1:

       INSERT INTO Employee
        (Name, EmpNo, DeptNo, DOB, Sex, EdLev) 
        VALUES ('Newhire A', 10025, '49/10/17', 'M',) ;

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

       Stage TIME(3) WITH DEFAULT

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

       Scale INTERVAL HOUR(2) WITH DEFAULT