Type Conversion Rules

The following table provides you with an overview of the permissible implicit conversions.

SOURCE TYPE	TARGET TYPE	DECIMAL	DOUBLE	BOOLEAN	DATE/TIMESTAMP	INTERVAL YEAR(p) TO MONTH	INTERVAL DAY(lfp) TO SECOND(fsp)	GEOMETRY	HASHTYPE	CHAR/VARCHAR
DECIMAL
DOUBLE
BOOLEAN
DATE/TIMESTAMP
INTERVAL YEAR(p) TO MONTH
INTERVAL DAY(p) TO SECOND(fp)
GEOMETRY
HASHTYPE
CHAR/VARCHAR

	= Implicit conversion always works
	= Implicit conversion possible, but errors may occur if single values cannot be converted
	= Implicit conversion is not possible

To maintain the performance improvements of using the HASHTYPE data type, implicit conversion between HASHTYPE and CHAR/VARCHAR data types is not supported in joins.

Usage notes

• When converting DECIMAL to DOUBLE, inaccuracies may occur with rounding.

• When converting a DECIMAL to a DECIMAL with a smaller scale (including the INTEGER alias), the value is rounded to the specified scale. For example, SELECT CAST(5.56 AS DECIMAL(3,1)); returns 5.6.

• When converting DECIMAL or DOUBLE to BOOLEAN, 1 is transformed to TRUE, 0 is transformed to FALSE, and NULL is transformed to NULL. For other data types, you can use the compatible representations of the values 1 and 0. For example, 1 = 1.00 (DECIMAL(3.2)) = 1.0 (DECIMAL(2.1)).

• When converting from BOOLEAN to DECIMAL or DOUBLE, TRUE is transformed to 1, FALSE is transformed to 0, and NULL is transformed to NULL.

• When converting from BOOLEAN to CHAR(n) or VARCHAR(n), TRUE is transformed to 'TRUE', and FALSE is transformed to 'FALSE'.

• Conversion from CHAR(n) or VARCHAR(n) to another data type is successful only if:

  • The data to be converted conforms to the target data type

  • All characters exist in the target character set

  • The value n is large enough

• When converting from CHAR(n) or VARCHAR(n) to BOOLEAN, you can use the strings '0', 'F', 'f', 'N', 'n', or 'FALSE' (case-insensitive) for the value FALSE, and '1', 'T', 't', 'y', 'Y', or 'TRUE' (case-insensitive) for the value TRUE.

• When converting from CHAR(n) or VARCHAR(n) to DATE or TIMESTAMP, you must take into account the current format model. For more information, see Date/Time Format Model.

• When there are operations with multiple operands (for example, the operators +,-,/,*), Exasol implicitly converts the operand to the data type with higher precedence (for example, DOUBLE is higher than DECIMAL) before executing the operation. This rule is also called numeric precedence.

• Conversion of GEOMETRY object with different spacial reference systems is not supported.

• When converting between GEOMETRY types and VARCHAR/CHAR data types, the maximum significant digits used when casting is defined with the ST_MAX_DECIMAL_DIGITS parameter and can be changed using the ALTER SYSTEM or ALTER SESSION statements. Note that the default value is 16, and the allowed range is <1,16>.

  For example, the table for POINT(40.7267, -74.0345) casted to string with precisions 1 to 16:

  ST_MAX_DECIMAL_DIGITS	Geometry as a string
  1	POINT(4e+01 -7e+01)
  2	POINT(41 -74)
  3	POINT(40.7 -74)
  4	POINT(40.73 -74.03)
  5	POINT(40.727 -74.034)
  6	POINT(40.7267 -74.0345)
  7	POINT(40.7267 -74.0345)
  8	POINT(40.7267 -74.0345)
  9	POINT(40.7267 -74.0345)
  10	POINT(40.7267 -74.0345)
  11	POINT(40.7267 -74.0345)
  12	POINT(40.7267 -74.0345)
  13	POINT(40.7267 -74.0345)
  14	POINT(40.7267 -74.0345)
  15	POINT(40.7267 -74.0345)
  16	POINT(40.7267 -74.03449999999999)

Example

In the following example, implicit conversion is done to insert the BOOLEAN entry into the DECIMAL column of the created table.

CREATE TABLE t(d DECIMAL);
-- Implicit conversion from BOOLEAN to DECIMAL
INSERT INTO t VALUES TRUE, NULL, FALSE;

SELECT * FROM t;