Most SQL databases provide a cast() function and DuckDB is no exception. The purpose of the cast() function is to convert a value from one data type to another. This can also be referred to as casting the value as the other data type.

When we cast a value to another data type, the corresponding value is returned in the specified data type. Normally this means that the value looks exactly the same, but there can be cases where it is adjusted to suit the new data type.

Syntax

The syntax goes like this:

CAST(expr AS type-name)

Where expr is the value we want to convert, and type-name is the new data type.

Example

Here’s a simple example to demonstrate:

SELECT cast( 123 AS varchar );

Here’s what that might look like when using duckbox mode in the DuckDB CLI:

+----------------------+
| CAST(123 AS VARCHAR) |
|        varchar       |
+----------------------+
| 123                  |
+----------------------+

When we use duckbox mode in the DuckDB CLI, the data type is shown in the column header.

Another way to get the data type is with the typeof() function. Here’s an example that shows the before and after data types of a value being cast:

SET VARIABLE theValue = 123;
SELECT 
    typeof(getvariable('theValue')) AS original,
    typeof(cast(getvariable('theValue') AS varchar)) AS converted;

Result:

+----------+-----------+
| original | converted |
+----------+-----------+
| INTEGER  | VARCHAR   |
+----------+-----------+

Truncation

In some cases the value may be truncated, depending on the value being converted. For example, if we convert a timestamp value to a date, then the time portion will be truncated.

Example:

SET VARIABLE theValue = timestamp '2029-01-01 18:30:00';
SELECT 
    getvariable('theValue') AS original,
    cast(getvariable('theValue') AS date) AS converted;

Result:

+---------------------+------------+
|      original       | converted  |
+---------------------+------------+
| 2029-01-01 18:30:00 | 2029-01-01 |
+---------------------+------------+

Rounding

Rounding can also occur in some cases. For example, if we cast a decimal value as an integer, the fractional part will be removed, and the resulting integer may be rounded up or down, depending on what the value of the fractional part was.

Example:

SET VARIABLE theValue = 10.50;
SELECT 
    getvariable('theValue') AS original,
    cast(getvariable('theValue') AS integer) AS converted;

Result:

+----------+-----------+
| original | converted |
+----------+-----------+
| 10.50    | 11        |
+----------+-----------+

In this case, the value 10.50 was rounded up to 11.

Here’s what happens when the decimal part is lower than .50:

SET VARIABLE theValue = 10.49;
SELECT 
    getvariable('theValue') AS original,
    cast(getvariable('theValue') AS integer) AS converted;

Result:

+----------+-----------+
| original | converted |
+----------+-----------+
| 10.49    | 10        |
+----------+-----------+

This time it was rounded down.

Shorthand

While the cast() function provides a nice, obvious way to explicitly perform data conversions, it’s not the only way. DuckDB also provides us with a shorthand method.

The shorthand syntax goes like this:  expr::TYPENAME

Example:

SET VARIABLE theValue = 10.86;
SELECT 
    getvariable('theValue') AS original,
    cast(getvariable('theValue') AS integer) AS converted_by_cast,
    getvariable('theValue')::integer AS converted_by_shorthand;

Result:

+----------+-------------------+------------------------+
| original | converted_by_cast | converted_by_shorthand |
+----------+-------------------+------------------------+
| 10.86    | 11                | 11                     |
+----------+-------------------+------------------------+

Here, we can see that both the second and third columns converted the value to an integer (and rounded up as appropriate). The second column used the cast() function, while the third column used the shorthand method.

Impossible Conversions

Not all values can be converted to another data type. For example, we can’t convert a a string like DuckDB to a numeric type, like integer.

When we try to perform a cast like this, we’ll get an error:

SELECT cast('DuckDB' AS integer);

Output:

Conversion Error:
Could not convert string 'DuckDB' to INT32

LINE 1: SELECT cast('DuckDB' AS integer);
               ^

Handling Errors with try_cast()

If you don’t want an error message to be returned whenever there’s an error like the one above, you can always use the try_cast() function instead of cast().

The difference between cast() and try_cast() is that try_cast() doesn’t throw an error in cases like the above. Instead, it simply returns NULL:

SELECT try_cast('DuckDB' AS integer);

Output:

+-------------------------------+
| TRY_CAST('DuckDB' AS INTEGER) |
+-------------------------------+
| NULL                          |
+-------------------------------+