General
The ANSI form of this phrase is documented in DEFAULT Phrase.
If you use a WITH DEFAULT phrase with a numeric range constraint, the range must include the system default value assigned to the data type defined for the column.
When a CREATE TABLE statement is processed, all WITH DEFAULT phrases are converted to DEFAULT phrases in which the system default value becomes the default constant_value.
Incompatibility Note
The WITH DEFAULT phrase is mutually exclusive with the DEFAULT phrase.
System Values for WITH DEFAULT Phrase
The value of a system default is determined by the data type defined for the column. The data types and associated system default values appear in the following table.
| Data Type | System Default |
|---|---|
| BIGINT | Zero |
| BYTE[(n)] | Zero if n omitted, or n binary zeros |
| BYTEINT | Zero |
| CHAR[(n)] | Depends on the server character set as follows:
|
| DATE | Current date |
| DECIMAL NUMERIC | Zero |
| DOUBLE PRECISION | Zero |
| FLOAT | Zero |
| INTEGER | Zero |
| INTERVAL DAY | INTERVAL ' 0 ' DAY |
| INTERVAL DAY TO HOUR | INTERVAL ' 0 00 ' DAY TO HOUR |
| INTERVAL DAY TO MINUTE | INTERVAL ' 0 00:00 ' DAY TO MINUTE |
| INTERVAL DAY TO SECOND | INTERVAL ' 0 00:00:00 ' DAY TO SECOND |
| INTERVAL HOUR | INTERVAL ' 0 ' HOUR |
| INTERVAL HOUR TO MINUTE | INTERVAL ' 00:00 ' HOUR TO MINUTE |
| INTERVAL HOUR TO SECOND | INTERVAL ' 00:00:00 ' HOUR TO SECOND |
| INTERVAL MINUTE | INTERVAL ' 0 ' MINUTE |
| INTERVAL MINUTE TO SECOND | INTERVAL ' 00:00 ' MINUTE TO SECOND |
| INTERVAL MONTH | INTERVAL ' 0 ' MONTH |
| INTERVAL SECOND | INTERVAL ' 0 ' SECOND |
| INTERVAL YEAR | INTERVAL ' 0 ' YEAR |
| INTERVAL YEAR TO MONTH | INTERVAL ' 0-00 ' YEAR TO MONTH |
| NUMBER | Zero |
| PERIOD(DATE) | The default value is set implicitly using a Period value constructor with the beginning argument set to CURRENT_DATE and the ending argument set to UNTIL_CHANGED. |
| PERIOD(TIMESTAMP) | The default value is set implicitly using a Period value constructor with the beginning argument set to CURRENT_TIMESTAMP[(n)] and the ending argument set to UNTIL_CHANGED. |
| REAL | Zero |
| SMALLINT | Zero |
| TIME | CURRENT_TIME |
| TIMESTAMP | CURRENT_TIMESTAMP |
| TIMESTAMP WITH TIME ZONE | CURRENT_TIMESTAMP |
| TIME WITH TIME ZONE | CURRENT_TIME |
| VARBYTE (n) | ' ' (null string) |
Unsupported Data Types
You cannot specify the WITH DEFAULT phrase for columns defined with the following data types:
- BLOB or CLOB
- UDT
- PERIOD(TIME)