Was this helpful?
Data Type Conversion Functions for Default Values
When you create or alter a table, 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, 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
Conversion Function
char(1)
char(' ')
c1
c(' ')
varchar(7)
varchar(' ')
long varchar
long_varchar(' ')
nchar
nchar(' ')
nvarchar
nvarchar (' ')
text(7)
text(' ')
byte(binary)
byte(0)
long byte (binary)
long_byte(0)
byte varying (binary)
varbyte(0)
integer (integer4)
int4(0)
smallint (integer2)
int2(0)
integer1
int1(0)
float (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)
ingresdate
ingresdate(' ') or ingresdate(null)
money
money(0)
object_key
object_key('01')
table_key
table_key('01')
If the new column is created with no conversion function, the defaults are:
varchar for character strings
float (float8) for floating point numbers
Either smallint (integer2) or integer (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.
Last modified date: 11/28/2023