17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

General

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)