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

Specifies that a user-defined default value is to be inserted in the field when a value is not specified for a column in an INSERT statement.

ANSI Compliance

DEFAULT is ANSI SQL:2011 compliant.

Also see the non-ANSI WITH DEFAULT Phrase.

Syntax

DEFAULT {
  constant_value |
  { DATE | TIME | TIMESTAMP } quotestring |
  INTERVAL [ sign ] quotestring qualifier
}

Syntax Elements

constant_value
A default value to be inserted when a column in the target table is omitted from the column_list specification of an INSERT statement.

When no DEFAULT phrase is provided, a NULL is inserted into the field for nullable columns, otherwise an error is returned.

A default value can be a keyword or built-in function. For more information on built-in functions, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

The following defines the valid keywords and functions:
  • number – specifies to insert a numeric literal value as the default value
  • NULL – specifies to insert a null (for nullable columns) as the default value
  • USER – specifies to insert the user name of the current user as the default value
  • CURRENT_DATE – specifies to insert the current system date as the default value
  • CURRENT_TIME – specifies to insert the current system time as the default value
  • CURRENT_TIMESTAMP – specifies to insert the current system date and time as the default value
DATE
A date value in ANSI DATE literal format as the insert default.
TIME
A time value in ANSI TIME literal format as the insert default.
TIMESTAMP
A timestamp value in ANSI TIMESTAMP literal format as the insert default.
INTERVAL
An interval value in ANSI INTERVAL format as the insert default.
quotestring
A default value represented as a string enclosed in apostrophes.
sign
The sign of an interval value.
qualifier
One of the following interval time periods:
  • YEAR
  • YEAR TO MONTH
  • MONTH
  • DAY
  • DAY TO HOUR
  • DAY TO MINUTE
  • DAY TO SECOND
  • HOUR
  • HOUR TO MINUTE
  • HOUR TO SECOND
  • MINUTE
  • MINUTE TO SECOND
  • SECOND