Typecasting is an operation that converts a value in one particular data type to the closest corresponding value in another data type. Like other SQL engines, DuckDB supports both implicit and explicit typecasting.

Explicit Casting

Explicit typecasting is performed by using a CAST expression. For example, CAST(col AS VARCHAR) or col::VARCHAR explicitly cast the column col to VARCHAR. See the cast page for more information.

Implicit Casting

In many situations, the system will add casts by itself. This is called implicit casting. This happens for example when a function is called with an argument that does not match the type of the function, but can be casted to the desired type.

Consider the function sin(DOUBLE). This function takes as input argument a column of type DOUBLE, however, it can be called with an integer as well: sin(1). The integer is converted into a double before being passed to the sin function.

Implicit casts can only be added for a number of type combinations, and is generally only possible when the cast cannot fail. For example, an implicit cast can be added from INTEGER to DOUBLE – but not from DOUBLE to INTEGER.

Casting Operations Matrix

Values of a particular data type cannot always be cast to any arbitrary target data type. The only exception is the NULL value – which can always be converted between types. The following matrix describes which conversions are supported. When implicit casting is allowed, it implies that explicit casting is also possible.

Even though a casting operation is supported based on the source and target data type, it does not necessarily mean the cast operation will succeed at runtime.

Deprecated Prior to version 0.10.0, DuckDB allowed any type to be implicitly cast to VARCHAR during function binding. Version 0.10.0 introduced a breaking change which no longer allows implicit casts to VARCHAR. The old_implicit_casting configuration option setting can be used to revert to the old behavior. However, please note that this flag will be deprecated in the future.

Lossy Casts

Casting operations that result in loss of precision are allowed. For example, it is possible to explicitly cast a numeric type with fractional digits like DECIMAL, FLOAT or DOUBLE to an integral type like INTEGER. The number will be rounded.

SELECT CAST(3.5 AS INTEGER);

Overflows

Casting operations that would result in a value overflow throw an error. For example, the value 999 is too large to be represented by the TINYINT data type. Therefore, an attempt to cast that value to that type results in a runtime error:

SELECT CAST(999 AS TINYINT);

Conversion Error: Type INT32 with value 999 can't be cast because the value is out of range for the destination type INT8

So even though the cast operation from INTEGER to TINYINT is supported, it is not possible for this particular value. TRY_CAST can be used to convert the value into NULL instead of throwing an error.

Varchar

The VARCHAR type acts as a univeral target: any arbitrary value of any arbitrary type can always be cast to the VARCHAR type. This type is also used for displaying values in the shell.

SELECT CAST(42.5 AS VARCHAR);

Casting from VARCHAR to another data type is supported, but can raise an error at runtime if DuckDB cannot parse and convert the provided text to the target data type.

SELECT CAST('NotANumber' AS INTEGER);

In general, casting to VARCHAR is a lossless operation and any type can be cast back to the original type after being converted into text.

SELECT CAST(CAST([1, 2, 3] AS VARCHAR) AS INTEGER[]);

Literal Types

Integer literals (such as 42) and string literals (such as 'string') have special implicit casting rules. See the literal types page for more information.

Lists / Arrays

Lists can be explicitly cast to other lists using the same casting rules. The cast is applied to the children of the list. For example, if we convert a INTEGER[] list to a VARCHAR[] list, the child INTEGER elements are individually cast to VARCHAR and a new list is constructed.

SELECT CAST([1, 2, 3] AS VARCHAR[]);

Arrays

Arrays follow the same casting rules as lists. In addition, arrays can be implicitly cast to lists of the same type. For example, an INTEGER[3] array can be implicitly cast to an INTEGER[] list.

Structs

Structs can be cast to other structs as long as the names of the child elements match.

SELECT CAST({'a': 42} AS STRUCT(a VARCHAR));

The names of the struct can also be in a different order. The fields of the struct will be reshuffled based on the names of the structs.

SELECT CAST({'a': 42, 'b': 84} AS STRUCT(b VARCHAR, a VARCHAR));

Unions

Union casting rules can be found on the UNION type page.