Data type conversion | ScopeDB Docs

A cast converts a value from one data type to another. Use explicit casts when an input does not have the type required by a function or operator.

Explicit casting vs implicit casting

An explicit cast names the target type. Implicit casting, or coercion, adapts a compatible value without a written cast.

Explicit casting

Use CAST(<expr> AS <type>), <expr>::<type>, or a typed literal. Convert an order amount and event time from text, then filter by time:

VALUES
    ('o-17', '7900', '2026-06-01T10:05:00Z'),
    ('o-42', '4900', '2026-06-01T09:55:00Z')
SELECT $0 AS order_id, CAST($1 AS int) AS total_cents, $2::timestamp AS event_time
WHERE event_time >= timestamp '2026-06-01T10:00:00Z'
SELECT order_id, total_cents, event_time

+----------+-------------+----------------------+
| order_id | total_cents | event_time           |
+----------+-------------+----------------------+
| o-17     | 7900        | 2026-06-01T10:05:00Z |
+----------+-------------+----------------------+

Invalid values raise an error. Wrap the expression in TRY(...) to return NULL when conversion fails.

Implicit casting (coercion)

When matching function arguments, ScopeQL accepts these conversions without an explicit cast:

NULL can match any argument type. The function may still reject NULL values.
A non-negative int constant can match uint.
A uint constant within the signed integer range can match int.
An integer constant can match float.

These constant conversions do not apply to numeric columns. Cast an integer column explicitly when a function requires float. For example, summarize integer latency measurements as a percentile:

VALUES (95), (120), (245), (1800)
SELECT $0 AS latency_ms
AGGREGATE quantile(latency_ms::float, quantile => 0.95) AS p95_ms

+--------+
| p95_ms |
+--------+
| 1800.0 |
+--------+

Casting and precedence

The :: operator binds more tightly than arithmetic and unary minus. Use parentheses to cast a calculated value, such as the total for an order line:

VALUES (4900, 3)
SELECT $0 AS price_cents, $1 AS quantity
SELECT (price_cents * quantity)::string || ' cents' AS line_total

+-------------+
| line_total  |
+-------------+
| 14700 cents |
+-------------+

Similarly, write (-0.0)::string to convert a negative number. Writing -0.0::string tries to negate a string and fails.

Data types that can be cast

Source	Target	Behavior
`int`, `uint`, `float`	Another numeric type	The value must fit the target range. Float-to-integer casts truncate toward zero; integer-to-float casts may lose precision.
`int`	`timestamp`, `interval`	Interpret the integer as nanoseconds from the Unix epoch or as a duration.
`timestamp`, `interval`	`int`	Return the corresponding nanosecond count.
`string`	Numeric types, `boolean`, `timestamp`, `interval`	Parse the text; invalid input raises an error.
Numeric types, `boolean`, `timestamp`, `interval`	`string`	Format the value as text.
`binary`	`string`	Format bytes as uppercase hexadecimal text.
`array`, `object`	`string`	Format the value as text. Use `to_json` when JSON serialization is required.
Any supported type	`any`	Preserve the value and its contained type.
`any`	Another supported type	Convert the contained value if it is compatible; otherwise raise an error.
`NULL`	Any supported type	Preserve NULL.

Casting a string to any keeps it as a string; it does not parse JSON. Use parse_json to parse JSON text, then cast to array or object when a function requires that type:

SELECT length(parse_json('["sku-17","sku-42"]')::array) AS item_count

+------------+
| item_count |
+------------+
| 2          |
+------------+