15.00 - DEFAULT - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

DEFAULT

Purpose  

Returns the current default value for the specified or derived column.

Syntax  

where:

 

Syntax element …

Specifies …

column_name

the name of a column in a base table, view, queue table, or derived table.

The column name can be qualified or unqualified.

ANSI Compliance

DEFAULT is partially ANSI SQL:2011 compliant.

The form of DEFAULT that specifies a column name is a Teradata extension. Using DEFAULT in a predicate is also a Teradata extension.

Result Type and Attributes

The result type, format, and title for DEFAULT(x) appear in the following table.

 

Data Type

Format

Title

Data type of the specified column

Format of the specified column

Default(x)

For information on data type default formats, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

Result Value

The DEFAULT function returns the default value of the specified column or derived column (if the column name is omitted).

If the specified or derived column is a view column or derived table column, the DEFAULT function returns the default value of the underlying table column.

If the default value of a column evaluates to a system variable, for example when the default value is CURRENT_TIME or USER, the DEFAULT function returns the value of the system variable at the time the statement is executed.

DEFAULT returns null when any of the following conditions are true:

  • The specified or derived column was defined with a DEFAULT NULL phrase
  • The specified or derived column has no explicit default value
  • The data type of the specified or derived column is UDT
  • The specified or derived column is the name of a view column that is derived from a single underlying table column that has no explicit default value
  • For an example, see “Example 3: Specifying a View Column Name” on page 294.

  • The specified or derived column is the name of a view column that is not derived from a single underlying table column, for example, the view column is derived from a literal expression
  • Omitting the Column Name

    You can use the form of DEFAULT that omits the column name under certain conditions in an INSERT, UPDATE, or MERGE statement or in a predicate clause that involves a comparison operation. The form of DEFAULT that omits the column name cannot be part of an expression.

    When the DEFAULT function does not specify a column name, Teradata Database derives the column based on context. For example, consider the following table definition:

       CREATE TABLE Manager
          (Emp_ID      INTEGER
          ,Dept_No     INTEGER DEFAULT 99
       );

    The following INSERT statement uses DEFAULT without a column name to insert the default value into the Dept_No column:

       INSERT INTO Manager VALUES (103499, DEFAULT);

    Using the DEFAULT function without specifying a column name can produce an error if Teradata Database cannot derive the column context.

    For an example that omits the column name when using the DEFAULT function in a predicate clause that involves a comparison operation, see “Example 2: Using DEFAULT in a Predicate” on page 293.

    For details on using the DEFAULT function in INSERT, UPDATE, and MERGE statements, see SQL Data Manipulation Language.

    Using a Qualified Column Name

    If you specify a qualified column name that includes the name of the table, you can use DEFAULT in a SELECT statement that has no FROM clause. For example, you can use the following statement to get the default value of the Dept_No column in the Manager table:

       SELECT DEFAULT(Manager.Dept_No);

    Restrictions

    The DEFAULT function cannot be used as a partitioning expression for defining PPIs.

    Error Conditions

    Using the DEFAULT function can result in an error when any of the following conditions are true:

  • The column name is omitted and Teradata Database cannot derive the column context
  • The DEFAULT function appears in a partitioning expression for defining PPIs
  • The column name is omitted and the DEFAULT function appears in an expression that does not support the DEFAULT function without a column name
  • The DEFAULT function appears in an expression for which the result type is incompatible
  • For example, consider the following table definition:

       CREATE TABLE Parts_Table
          (Part_Code   INTEGER DEFAULT 9999
          ,Part_Name   CHAR(20)
       );

    The following statement results in an error because the result type of the DEFAULT function is not compatible with the column to which the result is being compared:

       SELECT * FROM Parts_Table WHERE Part_Name = DEFAULT(Part_Code);

    Example : Inserting the Default Value Under Certain Conditions

    Consider the following Employee table definition:

       CREATE TABLE Employee
          (Emp_ID      INTEGER
          ,Last_Name   VARCHAR(30)
          ,First_Name  VARCHAR(30)
          ,Dept_No     INTEGER DEFAULT 99
       );

    The following statement uses DEFAULT to insert the default value of the Dept_No column when the supplied value is negative.

       USING (id INTEGER, n1 VARCHAR(30), n2 VARCHAR(30), dept INTEGER)
       INSERT INTO Employee VALUES
          (:id
          ,:n1
          ,:n2
          ,CASE WHEN (:dept < 0) THEN DEFAULT(Dept_No) ELSE :dept END
       );

    Example : Using DEFAULT in a Predicate

    The following statement uses DEFAULT to compare the values of the Dept_No column with the default value of the Dept_No column. Because the comparison operation involves a single column reference, Teradata Database can derive the column context of the DEFAULT function even though the column name is omitted.

       SELECT * FROM Employee WHERE Dept_No < DEFAULT;

    Note that if the DEFAULT function evaluates to null, the predicate is unknown and the WHERE condition is false.

    Example : Specifying a View Column Name

    Consider the DBC.HostsInfo system view, which has the following definition:

       REPLACE VIEW DBC.HostsInfo (LogicalHostId, HostName, DefaultCharSet)
       AS SELECT
           LogicalHostId
          ,HostName
          ,DefaultCharSet
       FROM DBC.Hosts WITH CHECK OPTION;

    The underlying table, DBC.Hosts, has the following definition:

       CREATE SET TABLE DBC.Hosts, FALLBACK, NO BEFORE JOURNAL, 
       NO AFTER JOURNAL, CHECKSUM = DEFAULT
          (LogicalHostId SMALLINT FORMAT 'ZZZ9' NOT NULL
          ,HostName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL
          ,DefaultCharSet VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC 
             NOT NULL)
       UNIQUE PRIMARY INDEX (LogicalHostId)
       UNIQUE INDEX (HostName);

    The following statement uses the DEFAULT function with the DBC.HostsInfo.HostName view column name:

       SELECT DISTINCT DEFAULT(HostName) FROM DBC.HostsInfo;

    The result of the DEFAULT function is null because the HostName view column is derived from a table column that has no explicit default value.

    Related Topics

     

    For information on …

    See …

    using predicates

    Chapter 20: “Logical Predicates.”

    comparison operations in predicates

    Chapter 10: “Comparison Operators and Functions.”

    the DEFAULT value control phrase

    SQL Data Types and Literals.

    INSERT, UPDATE, and MERGE statements

    SQL Data Manipulation Language.