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.
Syntax
WITH DEFAULT
ANSI Compliance
WITH DEFAULT is a Teradata extension to the ANSI SQL:2011 standard.
Usage Notes
The ANSI form of this phrase is documented in DEFAULT Phrase.
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.
Incompatibility Note
The WITH DEFAULT phrase is mutually exclusive with the DEFAULT phrase.
System Values for WITH 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:
|
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) |
Unsupported Data Types
- BLOB or CLOB
- UDT
- PERIOD(TIME)
Example: WITH DEFAULT Phrase
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',
Example: Inserting a Row Using a Default Value
The following INSERT statement adds a row containing a zero in the EdLev field defined in the previous example:
INSERT INTO Employee (Name, EmpNo, DeptNo, DOB, Sex, EdLev) VALUES ('Newhire A', 10025, 700, '49/10/17', 'M',) ;
Example: TIME Column With a Default of the Current Time
The following example shows a TIME column specified with a default of the current time:
Stage TIME(3) WITH DEFAULT
Example: INTERVAL Column With a Default Interval
The following example shows an INTERVAL column specified with a default interval:
Scale INTERVAL HOUR(2) WITH DEFAULT