11. Managing Tables and Views : Table Management : Data Type Conversion Functions for Default Values
 
Share this page                  
Data Type Conversion Functions for Default Values
When you create or alter a table, you can specify a default value for any column, which is used when no value is specified for the column. Instead of specifying a typical default value of zero or quoted spaces for a column, you may want to substitute a particular value as the default value for the new column. To do this, use the associated conversion function for the data type assigned to the new column.
The following table lists the data type and an example of its associated conversion function for creating a column:
Data Type
Default Conversion Function
char
char(' ')
c
c(' ')
VARCHAR
VARCHAR(' ')
INTEGER8
INT8(0)
INTEGER4
INT4(0)
INTEGER2
INT2(0)
INTEGER1
INT1(0)
FLOAT8
FLOAT8(0)
FLOAT4
FLOAT4(null)
DECIMAL
DECIMAL(0)
ANSIDATE
ANSIDATE('') or ANSIDATE(null)
TIME WITH TIME ZONE
TIME_WITH_TZ(' ') or TIME_WITH_TZ(null)
TIME WITHOUT TIME ZONE
TIME_WO_TZ(' ') or TIME_WO_TZ(null)
TIME WITH LOCAL TIME ZONE
TIME_LOCAL(' ') or TIME_LOCAL(null)
TIMESTAMP WITH TIME ZONE
TIMESTAMP_WITH_TZ(' ') or TIMESTAMP_WITH_TZ(null)
TIMESTAMP WITHOUT LOCAL TIME ZONE
TIMESTAMP_WO_TZ(' ') or TIMESTAMP_WO_TZ(null)
TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP_LOCAL(' ') or TIMESTAMP_LOCAL(null)
INTERVAL DAY TO SECOND
INTERVAL_DTOS(' ') or INTERVAL_DTOS(null)
INTERVAL YEAR TO MONTH
INTERVAL_YTOM(' ') or INTERVAL_YTOM(null)
MONEY
MONEY(0)
If the new column is created with no conversion function, the defaults are:
• VARCHAR for character strings
• FLOAT8 for floating point numbers
• Either INTEGER2 or INTEGER4 for integer numbers (depending on the size of the number)
To initialize a column’s value to null, specify the default value of null in any of the numeric conversion functions or the date function. Doing so makes the column nullable.
Important!  Do not use null as a default value for character fields—this causes an attempt to create a character field of null length, which cannot be done, and returns an error.