Type casting

A type cast converts a value from one data type to another. The conversion will only succeed if the value is compatible with the target type; otherwise, an error occurs.

CrateDB supports two equivalent syntaxes for type casting:

CAST(expression AS TYPE)
expression::TYPE

---

1. Cast Expressions

Syntax:

CAST(expression AS TYPE)
expression::TYPE

Examples:

-- Convert an HTTP port value to BOOLEAN
cr> SELECT CAST(port['http'] AS BOOLEAN) AS col
... FROM sys.nodes
... LIMIT 1;
+------+
| col  |
+------+
| TRUE |
+------+
SELECT 1 row in set (... sec)

-- Use shorthand (::) to cast to TEXT
cr> SELECT (2 + 10) / 2::TEXT AS col;
+-----+
| col |
+-----+
|   6 |
+-----+
SELECT 1 row in set (... sec)

-- Convert an array of integers to an array of booleans
cr> SELECT CAST([0, 1, 5] AS ARRAY(BOOLEAN)) AS active_threads;
+---------------------+
| active_threads      |
+---------------------+
| [false, true, true] |
+---------------------+
SELECT 1 row in set (... sec)

Casting to or from OBJECT, GEO_POINT, and GEO_SHAPE is not supported.

---

2. TRY_CAST

TRY_CAST works like CAST, but instead of raising an error for incompatible values, it returns NULL.

Syntax:

TRY_CAST(expression AS TYPE)

Examples:

-- Successful cast
cr> SELECT TRY_CAST('true' AS BOOLEAN) AS col;
+------+
| col  |
+------+
| TRUE |
+------+
SELECT 1 row in set (... sec)

-- Invalid cast returns NULL
cr> SELECT TRY_CAST(name AS INTEGER) AS name_as_int
... FROM sys.nodes
... LIMIT 1;
+-------------+
| name_as_int |
+-------------+
|        NULL |
+-------------+
SELECT 1 row in set (... sec)

---

3. Casting from String Literals

You can apply a type cast directly to a string literal to initialize a constant of the desired type.

Examples:

-- Integer constant
cr> SELECT INTEGER '25' AS int;
+-----+
| int |
+-----+
|  25 |
+-----+
SELECT 1 row in set (... sec)

-- Timestamp constant
cr> SELECT TIMESTAMP WITH TIME ZONE '2029-12-12T11:44:00.24446' AS ts;
+---------------+
| ts            |
+---------------+
| 1891770240244 |
+---------------+
SELECT 1 row in set (... sec)

String literal casting is supported only for primitive types. For complex types (e.g., ARRAY, OBJECT), use the cast expressions or CAST / TRY_CAST functions instead.

---

✅ Summary:

• Use CAST or :: for standard type conversions.

• Use TRY_CAST if you want to avoid errors and get NULL instead.

• Use string literal casts to create constants of primitive types.

• Complex type casting (e.g., arrays) must be done with CAST or TRY_CAST.