WITH DEFAULT Phrase | Data Types and Literals | Teradata Vantage - WITH 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 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:
  • 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)

Unsupported Data Types

You cannot specify the WITH DEFAULT phrase for columns defined with the following 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