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.
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 data types to numeric data types
Character data types to timestamp data types
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:
Simple expressions, such as:
comm + '500'
Boolean expressions, such as:
bonus > sal / '10'
Function and procedure calls, such as
MOD (counter, '2')
WHERE clause conditions, such as
WHERE hiredate = TO_DATE('1997-01-01','yyyy-mm-dd')
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 Type | BOOLEAN | SMALLINT | INTEGER | BIGINT | NUMERIC | REAL | DOUBLE PRECISION | CHAR | VARCHAR | TEXT | TIMESTAMP |
| BOOLEAN | Y | ||||||||||
| SMALLINT | Y | Y | Y | Y | Y | Y | Y | Y | Y | ||
| INTEGER | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
| BIGINT | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
| NUMERIC | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
| REAL | Y | Y | Y | Y | Y | Y | Y | Y | Y | ||
| DOUBLE PRECISION | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
| CHAR | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
| VARCHAR | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
| TEXT | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | |
| TIMESTAMP | Y | Y | Y | Y |
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.
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 Type | BOOLEAN | SMALLINT | INTEGER | BIGINT | NUMERIC | REAL | DOUBLE PRECISION | CHAR | VARCHAR | TEXT | TIMESTAMP |
| BOOLEAN | Y | ||||||||||
| SMALLINT | Y | Y | Y | Y | Y | Y | Y | Y | Y | ||
| INTEGER | Y | Y | Y | Y | Y | Y | Y | Y | Y | ||
| BIGINT | Y | Y | Y | Y | Y | Y | Y | Y | Y | ||
| NUMERIC | Y | Y | Y | Y | Y | Y | Y | Y | Y | ||
| REAL | Y | Y | Y | Y | Y | Y | Y | Y | Y | ||
| DOUBLE PRECISION | Y | Y | Y | Y | Y | Y | Y | Y | Y | ||
| CHAR | c | c | c | c | c | c | Y | Y | Y | c | |
| VARCHAR | c | c | c | c | c | c | Y | Y | Y | c | |
| TEXT | c | c | c | c | c | c | Y | Y | Y | c | |
| TIMESTAMP | Y | Y | Y | Y |
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.
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 Type | BOOLEAN | SMALLINT | INTEGER | BIGINT | NUMERIC | REAL | DOUBLE PRECISION | CHAR | VARCHAR | TEXT | TIMESTAMP |
| BOOLEAN | Y | ||||||||||
| SMALLINT | Y | Y | Y | Y | Y | Y | c | c | Y | ||
| INTEGER | c | Y | Y | Y | Y | Y | c | c | Y | ||
| BIGINT | c | c | Y | Y | Y | Y | c | c | Y | ||
| NUMERIC | c | c | c | Y | Y | Y | c | c | Y | ||
| REAL | c | c | c | c | Y | Y | c | c | Y | ||
| DOUBLE PRECISION | c | c | c | c | c | Y | c | c | Y | ||
| CHAR | c | c | c | c | c | c | Y | Y | Y | c | |
| VARCHAR | c | c | c | c | c | c | Y | Y | Y | c | |
| TEXT | c | c | c | c | c | c | Y | Y | Y | c | |
| TIMESTAMP | c | c | Y | Y |
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.
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 Type | BOOLEAN | SMALLINT | INTEGER | BIGINT | NUMERIC | REAL | DOUBLE PRECISION | CHAR | VARCHAR | TEXT | TIMESTAMP |
| BOOLEAN | Y | ||||||||||
| SMALLINT | Y | Y | Y | Y | Y | Y | c | c | c | ||
| INTEGER | Y | Y | Y | Y | Y | Y | c | c | c | ||
| BIGINT | Y | Y | Y | Y | Y | Y | c | c | c | ||
| NUMERIC | Y | Y | Y | Y | Y | Y | c | c | c | ||
| REAL | Y | Y | Y | Y | Y | Y | c | c | c | ||
| DOUBLE PRECISION | Y | Y | Y | Y | Y | Y | c | c | c | ||
| CHAR | c | c | c | c | c | c | Y | Y | Y | c | |
| VARCHAR | c | c | c | c | c | c | Y | Y | Y | c | |
| TEXT | c | c | c | c | c | c | Y | Y | Y | c | |
| TIMESTAMP | c | c | c | Y |
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