Type conversion
Explicit casts, try casts, implicit coercion and common-type selection.
Explicit conversion
CAST(value AS type) and the value::type shorthand convert a value or return an error.
SELECT CAST('2026-09-03' AS DATE),
'19.95'::DECIMAL(10, 2),
payload::JSON;TRY_CAST returns NULL when conversion fails:
SELECT raw_amount, TRY_CAST(raw_amount AS DECIMAL(18, 2)) AS amount
FROM staged_orders;Use TRY_CAST for dirty external input. Use CAST when invalid data should stop the query.
Conversion families
| From | To | Behavior |
|---|---|---|
| Smaller integer | Wider integer or decimal | Exact when the target range contains the value. |
| Decimal | Decimal | May round to the target scale; fails on target precision overflow. |
| Integer/decimal | Floating point | May lose precision. |
| Floating point | Integer/decimal | Applies numeric conversion and target range checks. |
| Text | Numeric, boolean, temporal, UUID, JSON | Parses the target's accepted textual form; TRY_CAST handles invalid input as NULL. |
| Date | Timestamp | Uses midnight in local civil time. |
| Timestamp | Date | Discards the time-of-day component. |
| Timestamp | Timestamptz | Uses the session time-zone interpretation where a zone is required. |
| JSON | LIST, ARRAY, STRUCT, MAP, UNION or VARIANT | Requires an explicit target shape. |
| LIST | ARRAY | Requires a compatible element type and exact fixed length. |
| ARRAY | LIST | Preserves element order and value type. |
Conversions remain subject to the actual source value. A type-level path being available does not guarantee every value fits the target.
Implicit coercion
VegaDB inserts an implicit cast only when the conversion is safe for the expression context. Common examples include widening compatible numeric values and choosing a shared type for CASE, list literals, comparisons and set operations.
SELECT CASE WHEN preferred THEN price_decimal ELSE fallback_integer END;Do not depend on implicit text-to-number or text-to-date parsing. Cast at the boundary so parsing failures and time-zone assumptions remain visible.
Combination casting
These forms require a common output type:
- branches of
CASE,ifandcoalesce; - elements of a list or array literal;
- corresponding columns of
UNION,INTERSECTandEXCEPT; - values compared by compatible operators;
- recursive CTE anchor and recursive terms.
If no safe common type exists, binding fails. Cast the competing values explicitly.
Nested conversion
Nested conversion is recursive. Each child must convert to its declared target.
SELECT json_transform(
payload,
'{"id":"BIGINT","lines":[{"sku":"VARCHAR","quantity":"INTEGER"}]}'
) AS typed_order
FROM raw_orders;For prepared parameters, bind canonical JSON and cast the placeholder to the exact nested SQL type:
SELECT $1::STRUCT(
id BIGINT,
tags VARCHAR[],
attributes MAP(VARCHAR, VARCHAR)
);Loss and overflow
- Narrowing integer casts fail when the value is outside the target range.
- Decimal casts can round fractional digits and then fail if precision is exceeded.
- Floating-point conversion can change exact decimal values.
- Timestamp precision conversion can discard subsecond digits.
- A time-zone conversion can be ambiguous or nonexistent around daylight-saving transitions.
Store money and identifiers in exact types. Convert presentation formats at the edge rather than inside durable table definitions.