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:
For an example, see “Example 3: Specifying a View Column Name” on page 294.
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:
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 |
|
|
comparison operations in predicates |
|
|
the DEFAULT value control phrase |
SQL Data Types and Literals. |
|
INSERT, UPDATE, and MERGE statements |
SQL Data Manipulation Language. |