15.10 - WITH DEFAULT Phrase - Teradata Database

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