The rules and restrictions are:
- The DEFAULT function accepts a single expression that identifies a relation column by name. The function evaluates to a value equal to the current default value for the column. For cases where the default value of the column is specified as a current built-in system function, the DEFAULT function evaluates to the current value of system variables at the time the request is executed.
The resulting data type of the DEFAULT function is the data type of the constant or built-in function specified as the default unless the default is NULL. If the default is NULL, the resulting date type of the DEFAULT function is the same as the data type of the column or expression for which the default is being requested.
- The DEFAULT function can be specified as DEFAULT or DEFAULT(column_name). When column name is not specified, the system derives the column based on context. If the column context cannot be derived, the system returns an error.
- You can specify a DEFAULT function with a column name in the select list of a SELECT statement. The DEFAULT function evaluates to the default value of the specified column.
- The DEFAULT function must include a column name in the expression list.
- You can determine the default value for a column by selecting it without specifying a FROM clause for the statement.
When you specify a SELECT statement that does not also specify a FROM clause, the system always returns only a single row with the default value of the column, regardless of how many rows are in the table. This is similar to the behavior of the TYPE function.
- When the column passed as an input argument to the DEFAULT function does not have an explicit default value associated with it, the DEFAULT function evaluates to null.
For more information about using DEFAULT within the SELECT statement, see:
- WHERE Clause
- HAVING Clause
- Rules and Restrictions for the QUALIFY Clause
- Rules for Using the DEFAULT Function as a Search Condition for an Outer Join ON Clause
- Inserting When Using a DEFAULT Function
- Rules for Using the DEFAULT Function With MERGE Requests
- Rules for Using the DEFAULT Function With Update