9.2. Implicit Data Type Conversion

No explicit casting is required when a source type can be implicitly converted to the target type without using any of the data type conversions given in the previous section.

The following sections summarize when implicit data type conversion occurs and when explicit casting, using the CAST function or other conversion function, is required.

9.2.1. Expressions and Operator Conversions

For expression evaluation, EnterpriseDB automatically performs most conversions implicitly. An expression is converted to a type based on its context. For example, operands to arithmetic operators are converted to numeric data types and operands to string functions are converted to character data types.

EnterpriseDB can automatically convert the following:

Character to numeric conversions succeed only if the character string represents a valid number. Character to timestamp conversions succeed only if the character string satisfies a valid date or timestamp format.

Some common types of expressions follow:

  1. Simple expressions, such as:

    • comm + '500'

  2. Boolean expressions, such as:

    • bonus > sal / '10'

  3. Function and procedure calls, such as

    • MOD (counter, '2')

  4. WHERE clause conditions, such as

    • WHERE hiredate = TO_DATE('1997-01-01','yyyy-mm-dd')

9.2.2. EDB/SPL Assignment Statement

The following table shows the implicit data type conversions performed in an EDB/SPL assignment statement. An assignment statement is of the form:

Target_variable := source_variable;

Table 9-1. Data Type Conversions for EDB/SPL Assignment Statement

     Target Type      
Source TypeBOOLEANSMALLINTINTEGERBIGINTNUMERICREALDOUBLE PRECISIONCHARVARCHARTEXTTIMESTAMP
BOOLEANY          
SMALLINT YYYYYYYYY 
INTEGER YYYYYYYYYY
BIGINT YYYYYYYYYY
NUMERIC YYYYYYYYYY
REAL YYYYYYYYY 
DOUBLE PRECISION YYYYYYYYYY
CHAR YYYYYYYYYY
VARCHAR YYYYYYYYYY
TEXT YYYYYYYYYY
TIMESTAMP       YYYY

Note:

Y - The source data is implicitly converted to the data type of the target when assigned to the target variable.

If the target variable is of integer type (SMALLINT, INTEGER, BIGINT) the source value must not have any decimal digits.

9.2.3. Assignment to Database Columns

The following table shows the implicit data type conversions performed when values are assigned to database columns in an INSERT or UPDATE command.

Certain cases require explicit casting of the source data to the target column data type. These are shown in the table as well.

Table 9-2. Data Type Conversions for Column Assignments

     Target Column Type      
Source TypeBOOLEANSMALLINTINTEGERBIGINTNUMERICREALDOUBLE PRECISIONCHARVARCHARTEXTTIMESTAMP
BOOLEANY          
SMALLINT YYYYYYYYY 
INTEGER YYYYYYYYY 
BIGINT YYYYYYYYY 
NUMERIC YYYYYYYYY 
REAL YYYYYYYYY 
DOUBLE PRECISION YYYYYYYYY 
CHAR ccccccYYYc
VARCHAR ccccccYYYc
TEXT ccccccYYYc
TIMESTAMP       YYYY

Note:

Y - The source data is implicitly converted to the data type of the target column

c - The source data must be explicitly cast to the data type of the target column.

If the target variable is of integer type (SMALLINT, INTEGER, BIGINT) the source value will be rounded to the nearest whole number.

9.2.4. Procedure and Function Calls

The following table shows the implicit data type conversions performed when values are passed from actual parameters to formal parameters in procedure or function calls.

Certain cases require explicit casting of the actual parameter to the target formal parameter data type. These are shown in the table as well.

Table 9-3. Data Type Conversions for Procedure and Function Calls

     Formal Parameter Type      
Actual Parameter TypeBOOLEANSMALLINTINTEGERBIGINTNUMERICREALDOUBLE PRECISIONCHARVARCHARTEXTTIMESTAMP
BOOLEANY          
SMALLINT YYYYYYccY 
INTEGER cYYYYYccY 
BIGINT ccYYYYccY 
NUMERIC cccYYYccY 
REAL ccccYYccY 
DOUBLE PRECISION cccccYccY 
CHAR ccccccYYYc
VARCHAR ccccccYYYc
TEXT ccccccYYYc
TIMESTAMP       ccYY

Note:

Y - The combination of actual parameter data type and formal parameter data type is permitted without casting.

c - The actual parameter must be explicitly cast to the data type of the target formal parameter.

9.2.5. Set Operations

SQL set operations such as UNION permit certain combinations of data types. These are summarized in the following table.

Table 9-4. Data Type Conversions for Set Operations

     Second Type      
First TypeBOOLEANSMALLINTINTEGERBIGINTNUMERICREALDOUBLE PRECISIONCHARVARCHARTEXTTIMESTAMP
BOOLEANY          
SMALLINT YYYYYYccc 
INTEGER YYYYYYccc 
BIGINT YYYYYYccc 
NUMERIC YYYYYYccc 
REAL YYYYYYccc 
DOUBLE PRECISION YYYYYYccc 
CHAR ccccccYYYc
VARCHAR ccccccYYYc
TEXT ccccccYYYc
TIMESTAMP       cccY

Note:

Y - The combination of data types are permitted in set operations without casting.

c - One data type must be cast to the other..

For numeric types, the resultant value takes the data type of the highest precedent numeric. The precedence order for numeric types from lowest to highest are:

SMALLINT--> INTEGER --> BIGINT --> NUMERIC --> REAL --> DOUBLE PRECISION