Data type conversions
SQL expressions can mix data of different types in the same expression. Most operations, however, require operands to have either the same type, or specific type combinations. The SQL compiler will in this case insert implicit type conversion operations, also called casts.
An explicit cast can be specified in three ways:
- using the
CAST
operator:CAST(value AS type)
- using an infix operator
::
from Postgres:value :: type
- using the
CONVERT
function:CONVERT(value, type)
The rules for implicit casts are complex; we inherit these rules from Calcite.
In general SQL casts may discard low order digits but never high order digits. A cast form a wide to a narrow datatype which cannot represent the value in the target type will generate a runtime error. Note however that casts to floating point values never generate runtime errors, since they use "infinity" values to represent out of range values.
Conversions from decimal and floating point types to integer types
always truncate the decimal digits (round towards zero). For example,
CAST(2.9 AS INTEGER)
returns 2, while CAST(-2.9 AS INTEGER)
returns -2.
Casts of strings to numeric types produce a runtime error when the string cannot be interpreted as a number.
Casts of strings to DATE
, TIME
, TIMESTAMP
produce the result
NULL
when the string does not have the correct format.
Casting a NULL
value to any type produces a NULL
result.
A value of type VARIANT
can be cast to any type and will produce a
nullable result; this kind of cast will never fail at runtime.
A value of any type can be cast to a VARIANT
type.