Default column and data type definitions

Certain columns and data types have predefined or assigned default values.

For example, default column values for the various data types are as follows:
  • NULL
  • 0 Used for small integer, integer, decimal, single-precision floating point, double-precision floating point, and decimal floating point data type.
  • Blank: Used for fixed-length and fixed-length double-byte character strings.
  • Zero-length string: Used for varying-length character strings, binary large objects, character large objects, and double-byte character large objects.
    Note: In Oracle compatibility mode, the default for varying-length character strings is a single blank, and the default for varying-length graphic strings is a single double-byte blank.
  • Date: This the system date at the time the row is inserted (obtained from the CURRENT_DATE special register). When a date column is added to an existing table, existing rows are assigned the date January, 01, 0001.
  • Time or Timestamp: This is the system time or system date/time of the at the time the statement is inserted (obtained from the CURRENT_TIME special register). When a time column is added to an existing table, existing rows are assigned the time 00:00:00 or a timestamp that contains the date January, 01, 0001 and the time 00:00:00.
    Note: All the rows get the same default time/timestamp value for a given statement.
  • Distinct user-defined data type: This is the built-in default value for the base data type of the distinct user-defined data type (cast to the distinct user-defined data type.