You can specify the DEFAULT function with a column name in the select projection list. The DEFAULT function evaluates to the default value of the specified column.
Assume this table definition for the examples below:
CREATE TABLE table14 ( col1 INTEGER, col2 INTEGER DEFAULT 10, col3 INTEGER DEFAULT 20, col4 CHARACTER(60) col5 TIMESTAMP DEFAULT NULL, col6 TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
The following query returns the default value of col2 and col3 of table14 in all the resulting rows. This is an inefficient form to get the default value of a column, because the system does a full table scan to perform the query. A more efficient method to get the default value of a column is to specify the query without a FROM clause.
SELECT DEFAULT(col2), DEFAULT(col3) FROM table14;
Assuming there are four rows in table14, the returned row set is as follows:
DEFAULT(col2) DEFAULT(col3) ------------ ------------ 10 20 10 20 10 20 10 20
If there are no rows in the table, then no rows are returned.
The following example returns the default value of col2 from table14. This helps discover the default value of a particular column. This is an inefficient form to get the default value of a column, because the system performs a full table scan to perform the query. A more efficient method to get the default value of a column is to specify the query without a FROM clause.
SELECT DISTINCT DEFAULT(col2) FROM Table14;
The returned row set is as follows:
DEFAULT(col2) DEFAULT(col3) ----------------------- -------------- 10 20
If there are no rows in the table, then no rows are returned.
When this SELECT statement does not specify a FROM clause, the system always returns a single row with the default value of the column, regardless of how many rows are in the table. This behavior is similar to the TYPE function.
SELECT DEFAULT(table14.col2);
The resulting row set is as follows:
DEFAULT(col2) ------------- 10
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.
Assume this table definition for the examples below:
CREATE TABLE table15 ( col1 INTEGER , col2 INTEGER NOT NULL, col3 INTEGER NOT NULL DEFAULT NULL, col4 INTEGER CHECK (col4 > 10) DEFAULT 9);
Because col1 does not have an explicit default value, the DEFAULT function evaluates to null.
SELECT DEFAULT(Table15.Col1);
The resulting row set is as follows:
DEFAULT(col1) ------------- ?
Because col2 does not have an explicit default value, the DEFAULT function evaluates to null. The function returns null even though the NOT NULL constraint on the column does not permit nulls in the column.
SELECT DEFAULT(table15.col2);
The resulting row set is as follows:
DEFAULT(col2) ------------- ?
Because col3 has an explicit default value of null, the DEFAULT function evaluates to its explicit default value. The function returns null even though the NOT NULL constraint on the column does not permit nulls in the column.
SELECT DEFAULT(table15.col3);
The resulting row set is as follows:
DEFAULT(col3) ------------- ?
Even though col4 has a default value 9, which violates the CHECK constraint col4>10, the DEFAULT function returns the value 9 even though check constraint does not permit this value in the column.
SELECT DEFAULT(Table15.Col4);
The resulting row set is as follows:
DEFAULT(Col4) ------------- 9