User Guide > User Guide > Managing Tables and Views > Table Management > Data Type Conversion Functions for Default Values
Was this helpful?
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(' ')
bigint (integer8)
int8(0)
integer (integer4)
int4(0)
smallint (integer2)
int2(0)
tinyint (integer1)
int1(0)
float (float8)
float8(0)
real (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
• 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: 03/21/2024