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 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')

9.2.2. SPL Assignment Statement

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

Target_variable := source_variable;

Table 9-1. Data Type Conversions for 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.

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 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.

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 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.

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 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