Type conversion functions

131 functions. Names resolve case-insensitively; an alias binds the same function as its canonical name. Each entry gives the signatures, the behaviour and an example.

cast

Converts a value to an explicitly declared SQL type, checking nested elements and numeric precision.

cast(array(T) AS json) -> json
cast(x:T AS T) -> T
cast(x:string|bool|int*|uint*|float*|decimal(P0,S0) AS int*) -> selected int*
cast(x:string|bool|int*|uint*|float*|decimal(P0,S0) AS uint*) -> selected uint*
cast(x:string|bool|int*|uint*|float*|decimal(P0,S0) AS float*) -> selected float*
cast(x:string|bool|int*|uint*|float*|decimal(P0,S0) AS decimal(P,S)) -> decimal(P,S)
cast(x:string|bool|int*|uint*|float*|decimal(P0,S0) AS bool) -> bool
cast(x:T AS string) -> string
cast(x:string|fixed_string(N)|bytes AS bytes) -> bytes
cast(x:string|bytes|fixed_string(M) AS fixed_string(N)) -> fixed_string(N)
cast(x:string|int*|uint*|date|datetime64(p,tz) AS date) -> date
cast(x:string|int*|uint*|float*|decimal(P,S)|date|datetime64(q,tz) AS datetime64(p,tz)) -> datetime64(p,tz)
cast(x:string|bytes|fixed_string(16)|uuid AS uuid) -> uuid
cast(x:string|uint32|ipv4 AS ipv4) -> ipv4
cast(x:string|bytes|fixed_string(16)|ipv4|ipv6 AS ipv6) -> ipv6
cast(x:string|int*|uint*|enum AS enum(E)) -> enum(E)
cast(x:string|json AS array(T)) -> array(T)
cast(x:array(S) AS array(T)) -> array(T)
cast(x:string|json AS map(string,V)) -> map(string,V)
cast(x:map(K,S) AS map(K,T)) -> map(K,T)
cast(x:string|json AS tuple(T1,...)) -> tuple(T1,...)
cast(x:tuple(S1,...) AS tuple(T1,...)) -> tuple(T1,...)
cast(x:T AS json) -> json
cast(x:json AS bool|int*|uint*|float*|decimal(P,S)|string|date|datetime64(p,tz)|uuid|ipv4|ipv6) -> selected scalar target
cast(x:enum(E) AS int16) -> int16

Converts the expression using a target descriptor fixed when the query binds, preserving SQL NULL. Numeric narrowing rejects lost information with NUMERIC_OVERFLOW; malformed literals raise INVALID_ARGUMENT or INVALID_DATETIME, and incompatible pairs raise TYPE_MISMATCH. Nested conversions recurse through supported pairs, retain map keys and require matching tuple arity; one invalid element fails the entire conversion. Casting text to JSON parses a document, whereas converting an array preserves order and emits JSON null for nullable elements.

Example

cast('42' AS INT32)
-- → 42

reinterpret_as_date

Reads four little-endian bytes as a calendar day offset without parsing or numeric conversion.

reinterpret_as_date(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> date

Interprets exactly four source bytes as the stored date day count in portable little-endian order. SQL NULL remains NULL; a width mismatch raises INVALID_ARGUMENT and a day count outside civil years 0001 through 9999 raises INVALID_DATETIME. The operation uses the NYX date layout, not a two-byte date encoding.

Example

reinterpret_as_date(cast(0 AS INT32))
-- → DATE '1970-01-01'

reinterpret_as_datetime

Decodes an eight-byte bit pattern into a UTC timestamp with whole-second precision.

reinterpret_as_datetime(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> datetime64(0,UTC)

Treats the input as an eight-byte little-endian timestamp payload and returns datetime64(0,UTC), without multiplying or rounding the underlying value. A NULL operand produces NULL. Inputs of another encoded width raise INVALID_ARGUMENT; decoded values violating timestamp invariants raise INVALID_DATETIME.

Example

reinterpret_as_datetime(cast(0 AS INT64))
-- → TIMESTAMP '1970-01-01 00:00:00 UTC'

reinterpret_as_fixed_string

Exposes a scalar’s binary storage as an exact-width byte string, retaining every zero byte.

reinterpret_as_fixed_string(x:int*|uint*|float*|date|datetime64(p,tz)|uuid) -> fixed_string(N)
reinterpret_as_fixed_string(x:bytes|fixed_string(M),N:uint32) -> fixed_string(N)

Returns the portable little-endian representation in fixed_string(N), deriving N from a scalar’s encoded width. Byte inputs instead require an explicit positive constant N equal to their length; invalid lengths raise INVALID_ARGUMENT. SQL NULL is preserved, and no character decoding or textual numeric formatting occurs.

Example

reinterpret_as_fixed_string(cast(65 AS UINT8))
-- → 'A'

reinterpret_as_float32

Interprets a four-byte payload as an IEEE 754 single-precision number with unchanged bits.

reinterpret_as_float32(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> float32

Uses the same 32 bits as the source to construct a float32, with portable little-endian byte order. This can change the numeric value completely, because integer digits are never converted arithmetically. NULL returns NULL, incompatible source types raise TYPE_MISMATCH and any encoded length other than four raises INVALID_ARGUMENT.

Example

reinterpret_as_float32(cast(1065353216 AS UINT32))
-- → 1.0

reinterpret_as_float64

Views eight raw bytes as a binary64 value, preserving its sign, exponent, and fraction fields.

reinterpret_as_float64(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> float64

Decodes an eight-byte little-endian bit representation into float64 rather than performing a checked numeric cast. The binary64 sign, exponent and significand come directly from the input bits, including special floating-point encodings. SQL NULL propagates; a source width other than eight bytes is INVALID_ARGUMENT.

Example

reinterpret_as_float64(cast(4607182418800017408 AS UINT64))
-- → 1.0

reinterpret_as_int128

Returns the signed interpretation of a sixteen-byte little-endian payload.

reinterpret_as_int128(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> int128

Reuses a 128-bit source representation as a two’s-complement int128, so the highest bit determines negativity. The input must materialize to exactly sixteen bytes; INVALID_ARGUMENT reports a different width and TYPE_MISMATCH reports an incompatible source kind. A SQL NULL input produces SQL NULL, without inspecting payload bytes.

Example

reinterpret_as_int128(cast(1 AS UINT128))
-- → 1

reinterpret_as_int16

Treats a two-byte sequence as a small signed integer using its high bit as the sign.

reinterpret_as_int16(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> int16

Returns the int16 encoded by two little-endian bytes, in the range -32768 through 32767. A value whose upper bit is set becomes negative even if its original type was unsigned. NULL remains NULL; the function raises INVALID_ARGUMENT when the materialized input is not exactly two bytes.

Example

reinterpret_as_int16(cast(65535 AS UINT16))
-- → -1

reinterpret_as_int256

Reuses thirty-two bytes as a signed 256-bit quantity without changing their representation.

reinterpret_as_int256(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> int256

Constructs int256 directly from a thirty-two-byte little-endian payload, interpreting the top bit through two’s-complement rules. It neither parses text nor saturates out-of-range numeric values, because all payload bits are retained. SQL NULL yields NULL and a length other than thirty-two raises INVALID_ARGUMENT.

Example

reinterpret_as_int256(cast(42 AS UINT256))
-- → 42

reinterpret_as_int32

Produces a signed 32-bit word from exactly four stored bytes, including negative bit patterns.

reinterpret_as_int32(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> int32

Reads the four-byte little-endian encoding as int32, whose range is -2147483648 through 2147483647. No overflow check compares source and target numeric magnitudes: identical bits may legitimately acquire a different sign. NULL is returned for NULL, while mismatched encoded widths raise INVALID_ARGUMENT.

Example

reinterpret_as_int32(cast(4294967295 AS UINT32))
-- → -1

reinterpret_as_int64

Recovers a signed machine-width integer from an eight-octet little-endian representation.

reinterpret_as_int64(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> int64

Reinterprets eight source bytes as int64 using the two’s-complement sign convention. Supported scalar inputs are first materialized in portable little-endian order; arbitrary-length blobs are not padded or truncated. SQL NULL stays NULL, and a payload length other than eight produces INVALID_ARGUMENT.

Example

reinterpret_as_int64(cast(18446744073709551615 AS UINT64))
-- → -1

reinterpret_as_int8

Turns one raw octet into a signed value between minus 128 and 127.

reinterpret_as_int8(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> int8

Uses the sole input byte as an int8 bit pattern, so byte values 128 through 255 represent -128 through -1. Only source representations exactly one byte long are accepted; other lengths raise INVALID_ARGUMENT. NULL remains NULL, and incompatible logical source types are rejected with TYPE_MISMATCH.

Example

reinterpret_as_int8(cast(255 AS UINT8))
-- → -1

reinterpret_as_string

Returns raw little-endian storage in a binary-capable SQL string, including embedded NUL characters.

reinterpret_as_string(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> string

Exposes the source’s portable little-endian bytes as a SQL string and preserves embedded zero bytes. The result is binary-capable storage, not UTF-8 formatting of a number, date or UUID. A NULL source returns NULL, and a source outside the listed scalar or byte overloads raises TYPE_MISMATCH.

Example

reinterpret_as_string(cast(65 AS UINT8))
-- → 'A'

reinterpret_as_uint128

Reads sixteen octets as a nonnegative 128-bit magnitude regardless of the source sign.

reinterpret_as_uint128(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> uint128

Interprets a sixteen-byte little-endian representation as uint128, covering zero through 2^128-1. Signed inputs keep their bits, so a negative source can become a large positive magnitude. SQL NULL propagates unchanged, while an input whose encoded size differs from sixteen bytes raises INVALID_ARGUMENT.

Example

reinterpret_as_uint128(cast(-1 AS INT128))
-- → 340282366920938463463374607431768211455

reinterpret_as_uint16

Decodes two little-endian octets into an unsigned value spanning zero through 65535.

reinterpret_as_uint16(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> uint16

Combines exactly two source bytes into uint16 with the first byte carrying the least significant bits. Unlike checked conversion, the signed pattern for -1 becomes 65535 rather than causing overflow. NULL produces NULL; wrong input widths raise INVALID_ARGUMENT and incompatible types raise TYPE_MISMATCH.

Example

reinterpret_as_uint16(cast(-1 AS INT16))
-- → 65535

reinterpret_as_uint256

Exposes all 256 payload bits as a positive-range integer from a thirty-two-byte input.

reinterpret_as_uint256(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> uint256

Returns uint256 by assigning a nonnegative magnitude to the complete thirty-two-byte little-endian representation. No bytes are sign-extended, discarded or reordered according to host architecture. SQL NULL is preserved, and the function reports INVALID_ARGUMENT unless the encoded source occupies exactly thirty-two bytes.

Example

reinterpret_as_uint256(cast(9 AS INT256))
-- → 9

reinterpret_as_uint32

Returns the unsigned four-byte word underlying a scalar or binary buffer.

reinterpret_as_uint32(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> uint32

Uses four little-endian bytes to build uint32, admitting every bit pattern from zero through 4294967295. An int32 sign bit becomes part of the unsigned magnitude, with no numeric overflow conversion. NULL returns NULL, and an encoded source length other than four bytes raises INVALID_ARGUMENT.

Example

reinterpret_as_uint32(cast(-1 AS INT32))
-- → 4294967295

reinterpret_as_uint64

Extracts an unsigned 64-bit interpretation from eight unchanged bytes.

reinterpret_as_uint64(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> uint64

Views an eight-byte source representation as uint64 in portable little-endian order. The full range zero through 18446744073709551615 is available, including encodings that were negative under a signed source type. SQL NULL stays NULL; a differently sized payload is INVALID_ARGUMENT.

Example

reinterpret_as_uint64(cast(-1 AS INT64))
-- → 18446744073709551615

reinterpret_as_uint8

Reads a single byte as an unsigned magnitude from 0 to 255.

reinterpret_as_uint8(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> uint8

Returns the one-byte input pattern as uint8, so a signed int8 value of -1 becomes 255. Multi-byte operands are rejected with INVALID_ARGUMENT rather than truncated to their least significant byte. A SQL NULL input remains NULL and an incompatible source type raises TYPE_MISMATCH.

Example

reinterpret_as_uint8(cast(-1 AS INT8))
-- → 255

reinterpret_as_uuid

Builds a UUID from sixteen bytes using the portable bit layout rather than RFC text parsing.

reinterpret_as_uuid(x:int*|uint*|float*|date|datetime64(p,tz)|uuid|bytes|fixed_string(N)) -> uuid

Constructs a UUID by reinterpreting exactly sixteen little-endian bytes of a supported source. This byte order is the bit-layout contract and is distinct from canonical UUID parsing in RFC byte order. SQL NULL returns NULL; a different payload length raises INVALID_ARGUMENT and invalid target invariants are rejected.

Example

reinterpret_as_uuid(cast(0 AS UINT128))
-- → UUID '00000000-0000-0000-0000-000000000000'

to_bool

Checks true/false text or exact numeric 0 and 1 to produce a Boolean.

to_bool(x:string|bool|int*|uint*|float*|decimal(P,S)) -> bool

Returns false for numeric zero or text false/0, and true for numeric one or text true/1; text matching is case-insensitive. Other numeric values and unrecognized text raise INVALID_ARGUMENT instead of using general truthiness. SQL NULL remains NULL and incompatible input types raise TYPE_MISMATCH.

Example

to_bool('TRUE')
-- → true

to_bool_or_default

Resolves recognized Boolean input, using a chosen logical default when recognition fails.

to_bool_or_default(x:string|bool|int*|uint*|float*|decimal(P,S)) -> bool
to_bool_or_default(x:string|bool|int*|uint*|float*|decimal(P,S),default:bool) -> bool

Parses true/false and 0/1 without case sensitivity, or accepts a numeric value exactly equal to zero or one. An unrecognized value returns the supplied bool default, with false used when the default is omitted; NULL input still yields NULL. TYPE_MISMATCH rejects a default whose type is not bool.

Example

to_bool_or_default('unknown', TRUE)
-- → true

to_bool_or_null

Produces a nullable truth value, marking unrecognized flags and nonbinary numbers as missing.

to_bool_or_null(x:string|bool|int*|uint*|float*|decimal(P,S)) -> bool

Converts recognized Boolean text and exact numeric 0 or 1 to bool. Malformed text or any other number produces SQL NULL, as does an input that was already NULL. Conversion failure is absorbed, but incompatible argument types remain a TYPE_MISMATCH at binding.

Example

to_bool_or_null('maybe')
-- → NULL

to_bool_or_zero

Maps invalid flag text to false while preserving existing SQL NULL values.

to_bool_or_zero(x:string|bool|int*|uint*|float*|decimal(P,S)) -> bool

Attempts the strict Boolean conversion accepting only true/false text, 0/1 text and numeric zero or one. A failed conversion returns false, the bool target zero; NULL input is not replaced by false. Incompatible source kinds raise TYPE_MISMATCH before conversion.

Example

to_bool_or_zero(2)
-- → false

to_date

Converts ISO date text, epoch-day counts, or zoned timestamps into a civil calendar date.

to_date(x:string|int*|uint*|date|datetime64(p,tz)) -> date

Parses ISO 8601 text, interprets integers as days from 1970-01-01, or takes the date in a timestamp’s descriptor timezone. The result must lie in civil years 0001 through 9999; invalid dates raise INVALID_DATETIME and overflow raises NUMERIC_OVERFLOW. SQL NULL propagates, with integer day counts checked against the int32 storage range.

Example

to_date(1)
-- → DATE '1970-01-02'

to_date_or_default

Returns a valid calendar day or a supplied replacement after date parsing or range failure.

to_date_or_default(x:string|int*|uint*|date|datetime64(p,tz)) -> date
to_date_or_default(x:string|int*|uint*|date|datetime64(p,tz),default:date) -> date

Uses ISO date parsing, epoch-day integer interpretation and timezone-aware timestamp-to-date conversion. If a value cannot become a date in years 0001 through 9999, it returns the typed default, or 1970-01-01 when omitted. SQL NULL remains NULL, and a default that is not a date raises TYPE_MISMATCH.

Example

to_date_or_default('2024-02-30')
-- → DATE '1970-01-01'

to_date_or_null

Marks impossible civil dates as NULL when interpreting text or temporal values.

to_date_or_null(x:string|int*|uint*|date|datetime64(p,tz)) -> date

Attempts conversion to date, with integer inputs counted in days since the Unix epoch and timestamp inputs read in their own timezone. Bad ISO text, overflow and dates outside years 0001 through 9999 become NULL instead of INVALID_DATETIME or NUMERIC_OVERFLOW. Existing SQL NULL also stays NULL; incompatible logical types raise TYPE_MISMATCH.

Example

to_date_or_null('2024-02-30')
-- → NULL

to_date_or_zero

Substitutes 1970-01-01 for failed calendar conversions while retaining genuine missing inputs.

to_date_or_zero(x:string|int*|uint*|date|datetime64(p,tz)) -> date

Returns a checked date from ISO text, an epoch-day count, a date or a timestamp interpreted in its descriptor timezone. Conversion failure produces the epoch date 1970-01-01, including invalid day-of-month and civil-range overflow. SQL NULL is preserved separately, and incompatible operand types still raise TYPE_MISMATCH.

Example

to_date_or_zero('not a date')
-- → DATE '1970-01-01'

to_datetime

Builds a whole-second timestamp from text, epoch seconds, dates, or existing temporal values.

to_datetime(x:string|int*|uint*|date|datetime64(p,tz),tz:string=UTC) -> datetime64(0,tz)

Converts integer inputs as Unix seconds and returns datetime64(0,tz), rejecting any loss of fractional precision with NUMERIC_OVERFLOW. The timezone is explicit, inherited from a temporal input, or UTC otherwise; malformed timestamp text raises INVALID_DATETIME. SQL NULL remains NULL, and invalid timezone arguments raise INVALID_ARGUMENT.

Example

to_datetime(1, 'UTC')
-- → TIMESTAMP '1970-01-01 00:00:01 UTC'

to_datetime_or_default

Falls back to a chosen seconds-resolution instant when temporal input cannot be converted.

to_datetime_or_default(x:string|int*|uint*|date|datetime64(p,tz),tz:string=UTC) -> datetime64(0,tz)
to_datetime_or_default(x:string|int*|uint*|date|datetime64(p,tz),tz:string=UTC,default:datetime64(0,tz)) -> datetime64(0,tz)

Produces datetime64(0,tz) from timestamp text, integer Unix seconds, dates or other timestamps. Parse failures and lost subsecond precision select the supplied matching default, or the Unix epoch when it is absent; SQL NULL still produces NULL. Timezone defaults follow the input descriptor before UTC, and invalid timezone configuration raises INVALID_ARGUMENT.

Example

to_datetime_or_default('invalid', 'UTC')
-- → TIMESTAMP '1970-01-01 00:00:00 UTC'

to_datetime_or_null

Represents failed whole-second timestamp conversions as SQL NULL without rounding away fractions.

to_datetime_or_null(x:string|int*|uint*|date|datetime64(p,tz),tz:string=UTC) -> datetime64(0,tz)

Attempts to construct datetime64(0,tz), using Unix seconds for integers and inheriting a temporal input’s timezone unless explicitly overridden. Invalid timestamp values, overflow and nonzero fractional seconds produce NULL; an already NULL operand does too. Incompatible types raise TYPE_MISMATCH and an invalid timezone raises INVALID_ARGUMENT.

Example

to_datetime_or_null('2024-01-01 00:00:00.5', 'UTC')
-- → NULL

to_datetime_or_zero

Replaces invalid second-granularity timestamp values with the epoch instant.

to_datetime_or_zero(x:string|int*|uint*|date|datetime64(p,tz),tz:string=UTC) -> datetime64(0,tz)

Returns a whole-second datetime64 timestamp, with integer arguments treated as Unix seconds. If parsing fails or the conversion would discard a fraction, the result is Unix epoch in the selected timezone; NULL input remains NULL. The timezone is explicit or inherited before falling back to UTC, and invalid timezone names raise INVALID_ARGUMENT.

Example

to_datetime_or_zero('invalid', 'UTC')
-- → TIMESTAMP '1970-01-01 00:00:00 UTC'

to_datetime64

Creates a timestamp at a chosen fractional precision by checking and rescaling Unix-second input.

to_datetime64(x:string|int*|uint*|float*|decimal(P,S)|date|datetime64(q,tz),p:uint8=3,tz:string=UTC) -> datetime64(p,tz)

Converts text, numeric Unix seconds and temporal inputs to datetime64(p,tz), with p defaulting to 3 and timezone defaulting to UTC. Precision must be a constant from zero through nine; INVALID_ARGUMENT rejects an invalid descriptor, INVALID_DATETIME rejects malformed temporal text and NUMERIC_OVERFLOW rejects lossy rescaling. NULL input gives NULL, including when the target is more precise than the source.

Example

to_datetime64('2024-01-01 00:00:00.125', 3, 'UTC')
-- → TIMESTAMP '2024-01-01 00:00:00.125 UTC'

to_datetime64_or_default

Returns a fractional timestamp or a matching replacement if a value cannot fit the requested scale.

to_datetime64_or_default(x:string|int*|uint*|float*|decimal(P,S)|date|datetime64(q,tz),p:uint8=3,tz:string=UTC) -> datetime64(p,tz)
to_datetime64_or_default(x:string|int*|uint*|float*|decimal(P,S)|date|datetime64(q,tz),p:uint8=3,tz:string=UTC,default:datetime64(p,tz)) -> datetime64(p,tz)

Checks input against datetime64(p,tz), interpreting numeric values as Unix seconds and keeping only exactly representable fractional precision. A failed value conversion returns a supplied default with that descriptor, or Unix epoch if omitted; SQL NULL is preserved. Precision outside zero through nine or an invalid timezone remains INVALID_ARGUMENT, and a mismatched default raises TYPE_MISMATCH.

Example

to_datetime64_or_default('invalid', 3, 'UTC')
-- → TIMESTAMP '1970-01-01 00:00:00.000 UTC'

to_datetime64_or_null

Yields NULL for timestamp text or fractional seconds that cannot satisfy the selected precision.

to_datetime64_or_null(x:string|int*|uint*|float*|decimal(P,S)|date|datetime64(q,tz),p:uint8=3,tz:string=UTC) -> datetime64(p,tz)

Returns datetime64(p,tz) when the source can be converted without overflowing or losing fractional information. Malformed timestamp values and unrepresentable rescaling return NULL, while SQL NULL input is also unchanged. The constant precision must still be zero through nine and the timezone valid; descriptor errors raise INVALID_ARGUMENT.

Example

to_datetime64_or_null('invalid', 6, 'UTC')
-- → NULL

to_datetime64_or_zero

Uses the Unix epoch at the requested subsecond scale when timestamp conversion fails.

to_datetime64_or_zero(x:string|int*|uint*|float*|decimal(P,S)|date|datetime64(q,tz),p:uint8=3,tz:string=UTC) -> datetime64(p,tz)

Attempts a checked datetime64(p,tz) conversion with numeric values measured in Unix seconds. Value parsing, range and precision failures produce the epoch represented at p digits; they do not replace an existing SQL NULL. An invalid precision outside zero through nine or an invalid timezone raises INVALID_ARGUMENT rather than producing an epoch.

Example

to_datetime64_or_zero('invalid', 6, 'UTC')
-- → TIMESTAMP '1970-01-01 00:00:00.000000 UTC'

to_decimal

Produces an exact decimal with caller-selected precision and scale, rejecting discarded digits.

to_decimal(x:string|bool|int*|uint*|float*|decimal(P0,S0),P:uint8,S:uint8) -> decimal(P,S)

Converts text, Boolean or numeric input to decimal(P,S), with constant P from 1 through 76 and constant S from zero through P. Invalid descriptors raise INVALID_ARGUMENT, malformed numeric text raises INVALID_ARGUMENT and overflow or lossy scale reduction raises NUMERIC_OVERFLOW. SQL NULL stays NULL; Boolean false and true convert to zero and one.

Example

to_decimal('12.50', 6, 2)
-- → 12.50

to_decimal_or_default

Applies a user-defined decimal descriptor, selecting its typed default for unrepresentable values.

to_decimal_or_default(x:string|bool|int*|uint*|float*|decimal(P0,S0),P:uint8,S:uint8) -> decimal(P,S)
to_decimal_or_default(x:string|bool|int*|uint*|float*|decimal(P0,S0),P:uint8,S:uint8,default:decimal(P,S)) -> decimal(P,S)

Converts the source to decimal(P,S) with constant precision P from 1 through 76 and scale S from zero through P. A failed value conversion returns a matching supplied default, or zero with scale S when omitted; input NULL stays NULL. Invalid precision or scale raises INVALID_ARGUMENT and a default of another descriptor raises TYPE_MISMATCH.

Example

to_decimal_or_default('bad', 5, 2, to_decimal('7.25', 5, 2))
-- → 7.25

to_decimal_or_null

Returns NULL when a value cannot satisfy explicitly chosen decimal precision and scale.

to_decimal_or_null(x:string|bool|int*|uint*|float*|decimal(P0,S0),P:uint8,S:uint8) -> decimal(P,S)

Checks input against decimal(P,S), preserving its value exactly or returning NULL for malformed, overflowing or lossy conversions. A NULL input also returns NULL. Precision and scale remain constant query parameters with 1<=P<=76 and 0<=S<=P; violations raise INVALID_ARGUMENT rather than becoming a data NULL.

Example

to_decimal_or_null('123.45', 4, 2)
-- → NULL

to_decimal_or_zero

Supplies zero with a custom decimal scale when parsing or precision checks reject the source.

to_decimal_or_zero(x:string|bool|int*|uint*|float*|decimal(P0,S0),P:uint8,S:uint8) -> decimal(P,S)

Attempts to represent the input exactly as decimal(P,S), replacing failed value conversions with zero of that precision and scale. This replacement covers invalid numeric text and numeric overflow, but never overwrites SQL NULL. P and S must be constants satisfying 1<=P<=76 and 0<=S<=P, otherwise INVALID_ARGUMENT is raised.

Example

to_decimal_or_zero('123.45', 4, 2)
-- → 0.00

to_decimal_string

Formats numeric input as decimal text, preserving its scale or rounding to an explicit number of places.

to_decimal_string(x:decimal(P,S)) -> string
to_decimal_string(x:int*|uint*|float*|decimal(P,S),scale:uint8) -> string

Without a scale argument, serializes a decimal using its exact declared fractional scale, including trailing zeros. An explicit scale from zero through 76 formats numeric input with half-away-from-zero rounding; invalid scale raises INVALID_ARGUMENT. NULL yields NULL, and the one-argument overload requires a decimal rather than an arbitrary number.

Example

to_decimal_string(to_decimal('1.25', 3, 2), 1)
-- → '1.3'

to_decimal128

Checks a number against 38 decimal digits and an explicit fractional scale.

to_decimal128(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(38,S)

Returns decimal(38,S), where S is a constant between zero and 38 and the width name does not introduce a separate logical type. Malformed input raises INVALID_ARGUMENT, while exceeding the precision or discarding nonzero fractional digits raises NUMERIC_OVERFLOW. NULL propagates without creating a numeric zero.

Example

to_decimal128('123.45', 2)
-- → 123.45

to_decimal128_or_default

Uses a typed default when a value cannot occupy a 38-digit scaled decimal.

to_decimal128_or_default(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(38,S)
to_decimal128_or_default(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8,default:decimal(38,S)) -> decimal(38,S)

Attempts to store the source exactly in decimal(38,S), preserving all fractional digits that the chosen constant scale allows. Value conversion failure selects the supplied decimal(38,S) default, or scaled zero when omitted, while a NULL source stays NULL. Scales outside zero through 38 raise INVALID_ARGUMENT and a default with the wrong descriptor raises TYPE_MISMATCH.

Example

to_decimal128_or_default('invalid', 2)
-- → 0.00

to_decimal128_or_null

Returns a nullable high-precision decimal, rejecting unsuitable values without a conversion exception.

to_decimal128_or_null(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(38,S)

Converts the value into decimal(38,S) and returns NULL if parsing, magnitude checking or exact rescaling fails. S must still be a constant within zero through 38; INVALID_ARGUMENT reports an invalid scale instead of masking a query-definition error. SQL NULL remains NULL, and incompatible input kinds raise TYPE_MISMATCH.

Example

to_decimal128_or_null('1.234', 2)
-- → NULL

to_decimal128_or_zero

Produces scaled decimal zero after failed conversion within a 38-digit capacity.

to_decimal128_or_zero(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(38,S)

Returns the input as decimal(38,S) when it fits exactly, otherwise the numeric zero encoded at that same scale. Malformed text, precision overflow and discarded fractional digits use this zero policy, but SQL NULL stays NULL. A nonconstant scale or a scale outside zero through 38 raises INVALID_ARGUMENT.

Example

to_decimal128_or_zero('1.234', 2)
-- → 0.00

to_decimal256

Converts supported input to a wide decimal carrying as many as 76 significant digits.

to_decimal256(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(76,S)

Constructs decimal(76,S) with a constant fractional scale from zero through 76, preserving exact numeric content. Values exceeding that precision or requiring lossy rescaling raise NUMERIC_OVERFLOW; malformed numeric text and invalid scales raise INVALID_ARGUMENT. SQL NULL produces NULL, and decimal256 names this descriptor rather than another logical type.

Example

to_decimal256('1234567890123456789012345678901234567890', 0)
-- → 1234567890123456789012345678901234567890

to_decimal256_or_default

Resolves a 76-digit decimal conversion using a supplied replacement when exact storage fails.

to_decimal256_or_default(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(76,S)
to_decimal256_or_default(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8,default:decimal(76,S)) -> decimal(76,S)

Parses or converts the source into decimal(76,S), accepting a constant S between zero and 76. An invalid value chooses the default of that exact descriptor, with scaled zero used when no default is supplied; input NULL bypasses the default and remains NULL. Invalid descriptor arguments raise INVALID_ARGUMENT and incorrectly typed defaults raise TYPE_MISMATCH.

Example

to_decimal256_or_default('bad', 3)
-- → 0.000

to_decimal256_or_null

Marks unsuccessful 76-digit decimal parsing or rescaling as a missing numeric result.

to_decimal256_or_null(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(76,S)

Returns a decimal(76,S) only when the source can be represented without magnitude overflow or fractional loss. Failed numeric parsing and inexact conversions produce SQL NULL, preserving preexisting NULL as well. The constant scale must lie from zero through 76; invalid scale configuration still raises INVALID_ARGUMENT.

Example

to_decimal256_or_null('bad', 4)
-- → NULL

to_decimal256_or_zero

Replaces unusable wide-decimal values with zero at the requested fraction depth.

to_decimal256_or_zero(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(76,S)

Attempts exact conversion into the 76-digit decimal descriptor at scale S, replacing value failures with decimal(76,S) zero. SQL NULL is retained, so a missing reading remains distinct from a malformed reading that becomes zero. A constant scale outside zero through 76 is INVALID_ARGUMENT, and incompatible source kinds are TYPE_MISMATCH.

Example

to_decimal256_or_zero('bad', 4)
-- → 0.0000

to_decimal32

Fits text or numeric input into a nine-digit decimal at a specified scale.

to_decimal32(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(9,S)

Converts the source exactly to decimal(9,S), reserving S of the nine digits for the fractional portion. S must be a constant from zero through nine; invalid scale or malformed text raises INVALID_ARGUMENT. Overflow and lost fractional information raise NUMERIC_OVERFLOW, while NULL input stays NULL.

Example

to_decimal32('19.95', 2)
-- → 19.95

to_decimal32_or_default

Chooses a replacement for values that cannot fit a compact nine-digit decimal.

to_decimal32_or_default(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(9,S)
to_decimal32_or_default(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8,default:decimal(9,S)) -> decimal(9,S)

Returns decimal(9,S) after checked parsing or conversion, substituting a matching default when the value is invalid or inexact. Without a supplied default the replacement is scaled zero, and SQL NULL remains NULL in either case. The scale must be constant within zero through nine; INVALID_ARGUMENT rejects its invalid settings and TYPE_MISMATCH rejects an incompatible default.

Example

to_decimal32_or_default('bad', 2, to_decimal32('9.99', 2))
-- → 9.99

to_decimal32_or_null

Turns compact-decimal parse failures and excess fractional digits into NULL.

to_decimal32_or_null(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(9,S)

Checks whether the source fits decimal(9,S) without discarding information and returns NULL when it does not. This includes malformed text, too many integer digits and nonzero digits beyond S; an original NULL is also retained. Scale is bind-time metadata constrained to zero through nine, with INVALID_ARGUMENT for an invalid declaration.

Example

to_decimal32_or_null('1000000000', 0)
-- → NULL

to_decimal32_or_zero

Returns a nine-digit scaled value, replacing invalid numeric input with decimal zero.

to_decimal32_or_zero(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(9,S)

Converts supported input to decimal(9,S) or supplies that descriptor’s zero when parsing, range or exactness checks fail. The default does not apply to SQL NULL, which remains NULL. S is a constant between zero and nine; an invalid scale raises INVALID_ARGUMENT rather than selecting the zero default.

Example

to_decimal32_or_zero('1000000000', 2)
-- → 0.00

to_decimal64

Creates an 18-digit decimal from text or numbers without silently rounding the fraction.

to_decimal64(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(18,S)

Returns decimal(18,S), using a constant scale from zero through 18 and leaving at most 18-S integer digits. Invalid numeric syntax raises INVALID_ARGUMENT, while out-of-range magnitude or scale reduction that loses information raises NUMERIC_OVERFLOW. SQL NULL remains NULL, and an invalid scale declaration is INVALID_ARGUMENT.

Example

to_decimal64('1234567890.125', 3)
-- → 1234567890.125

to_decimal64_or_default

Falls back to an assigned amount when exact conversion to an 18-digit decimal fails.

to_decimal64_or_default(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(18,S)
to_decimal64_or_default(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8,default:decimal(18,S)) -> decimal(18,S)

Tries to produce decimal(18,S) while retaining the source’s numeric value exactly. A failed conversion returns a default with the same precision and scale, or scaled zero if none was provided; SQL NULL remains NULL independently. Constant scale S must be zero through 18 or INVALID_ARGUMENT is raised, and a mismatched replacement is TYPE_MISMATCH.

Example

to_decimal64_or_default('missing', 2, to_decimal64('1.00', 2))
-- → 1.00

to_decimal64_or_null

Returns missing for malformed or inexact values under an 18-digit decimal contract.

to_decimal64_or_null(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(18,S)

Produces decimal(18,S) for valid input and NULL when text parsing, precision bounds or exact rescaling cannot be satisfied. Existing NULL values pass through unchanged, allowing the result to distinguish accepted amounts from unusable ones. Scale configuration remains checked: nonconstant S or a value outside zero through 18 raises INVALID_ARGUMENT.

Example

to_decimal64_or_null('12.345', 2)
-- → NULL

to_decimal64_or_zero

Substitutes an 18-digit decimal’s scaled zero for failed amount conversion.

to_decimal64_or_zero(x:string|bool|int*|uint*|float*|decimal(P0,S0),S:uint8) -> decimal(18,S)

Returns an exact decimal(18,S) when possible, with conversion errors mapped to the target zero instead of throwing NUMERIC_OVERFLOW or a value-parse INVALID_ARGUMENT. SQL NULL still yields NULL. The zero policy does not relax descriptor validation: S must be a constant between zero and 18.

Example

to_decimal64_or_zero('12.345', 2)
-- → 0.00

to_fixed_string

Pads text or bytes with zeros to a constant byte width, rejecting values that are too long.

to_fixed_string(x:string|bytes|fixed_string(M),N:uint32) -> fixed_string(N)

Returns fixed_string(N) by copying the input bytes and zero-padding any unused suffix. N must be a compile-time constant from 1 through 1048576; invalid N or an input exceeding N bytes raises INVALID_ARGUMENT rather than truncating data. SQL NULL stays NULL, and multibyte characters count by bytes rather than character positions.

Example

to_fixed_string('AB', 2)
-- → 'AB'

to_float32

Produces a checked single-precision floating-point value from textual, Boolean, or numeric input.

to_float32(x:string|bool|int*|uint*|float*|decimal(P,S)) -> float32

Converts the source to float32 and checks that numeric narrowing does not overflow or lose information. Malformed numeric text raises INVALID_ARGUMENT, while range or checked-precision failure raises NUMERIC_OVERFLOW. SQL NULL propagates and incompatible logical source types raise TYPE_MISMATCH.

Example

to_float32('1.5')
-- → 1.5

to_float32_or_default

Selects a float32 replacement when numeric input cannot be converted to single precision.

to_float32_or_default(x:string|bool|int*|uint*|float*|decimal(P,S)) -> float32
to_float32_or_default(x:string|bool|int*|uint*|float*|decimal(P,S),default:float32) -> float32

Attempts the same checked float32 conversion as the strict form, including numeric precision and range checks. Failure returns the supplied float32 default or 0.0 when omitted, while SQL NULL remains NULL. A default of another logical type is TYPE_MISMATCH; it is not automatically coerced by this function.

Example

to_float32_or_default('bad', to_float32('2.5'))
-- → 2.5

to_float32_or_null

Returns nullable single precision, marking malformed or lossy numeric input as missing.

to_float32_or_null(x:string|bool|int*|uint*|float*|decimal(P,S)) -> float32

Parses or converts the source to float32 only when the checked conversion succeeds. Overflow, malformed text and lossy numeric narrowing produce SQL NULL instead of their value-conversion errors. NULL input is also retained, and incompatible argument kinds still produce TYPE_MISMATCH.

Example

to_float32_or_null('bad')
-- → NULL

to_float32_or_zero

Uses floating zero when a reading cannot be represented by the checked 32-bit target.

to_float32_or_zero(x:string|bool|int*|uint*|float*|decimal(P,S)) -> float32

Returns a float32 after checked conversion, substituting 0.0 on malformed input, overflow or loss of numeric information. A source that is SQL NULL stays NULL rather than being classified as a failed conversion. TYPE_MISMATCH remains an error for operands outside the supported text, Boolean and numeric domains.

Example

to_float32_or_zero('bad')
-- → 0.0

to_float64

Converts recognized numeric text and scalar numbers to a checked double-precision result.

to_float64(x:string|bool|int*|uint*|float*|decimal(P,S)) -> float64

Returns float64 from textual, Boolean, integer, floating or decimal input, rejecting checked conversions that overflow or lose required information. INVALID_ARGUMENT reports malformed numeric syntax and NUMERIC_OVERFLOW reports a failed range or precision check. SQL NULL produces NULL; supported identity conversions keep the source value.

Example

to_float64('2.25')
-- → 2.25

to_float64_or_default

Returns a double or a caller-selected floating default after unsuccessful source conversion.

to_float64_or_default(x:string|bool|int*|uint*|float*|decimal(P,S)) -> float64
to_float64_or_default(x:string|bool|int*|uint*|float*|decimal(P,S),default:float64) -> float64

Attempts a checked conversion to float64 and returns a matching typed default when parsing, range or precision validation fails. If the default is omitted, the result on failure is 0.0; an input NULL is preserved separately. A default with a different logical type or an incompatible source raises TYPE_MISMATCH.

Example

to_float64_or_default('invalid', to_float64('3.5'))
-- → 3.5

to_float64_or_null

Marks rejected double-precision conversions as NULL, including invalid numeric syntax.

to_float64_or_null(x:string|bool|int*|uint*|float*|decimal(P,S)) -> float64

Converts the input to float64 when its syntax, magnitude and required precision satisfy the checked conversion rules. Failed value conversions return NULL, and an input already holding SQL NULL also returns NULL. This policy does not accept new source types: incompatible arguments remain TYPE_MISMATCH.

Example

to_float64_or_null('invalid')
-- → NULL

to_float64_or_zero

Provides double-precision zero for failed numeric parsing or checked representation changes.

to_float64_or_zero(x:string|bool|int*|uint*|float*|decimal(P,S)) -> float64

Returns the checked float64 conversion of valid input and 0.0 when value conversion fails. Malformed text, overflow and lossy numeric conversion follow this default, whereas SQL NULL remains NULL. The source must still belong to a supported scalar domain or TYPE_MISMATCH is raised.

Example

to_float64_or_zero('invalid')
-- → 0.0

to_int128

Checks a textual or numeric value for exact storage in the signed 128-bit integer range.

to_int128(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int128

Returns int128 when the source is an exact integer between -2^127 and 2^127-1. Fractional values and magnitudes outside that interval raise NUMERIC_OVERFLOW; malformed integer text raises INVALID_ARGUMENT. SQL NULL stays NULL, and Boolean input becomes zero or one.

Example

to_int128('170141183460469231731687303715884105727')
-- → 170141183460469231731687303715884105727

to_int128_or_default

Selects a wide signed replacement for values that cannot become a 128-bit whole number.

to_int128_or_default(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int128
to_int128_or_default(x:string|bool|int*|uint*|float*|decimal(P,S),default:int128) -> int128

Attempts exact conversion to the signed range -2^127 through 2^127-1 without truncating fractional digits. Invalid text, nonintegral input or range failure returns the supplied int128 default, or integer zero when absent; NULL input remains NULL. A replacement outside the int128 type contract raises TYPE_MISMATCH.

Example

to_int128_or_default('bad', to_int128('-7'))
-- → -7

to_int128_or_null

Returns NULL for malformed, fractional, or out-of-range input to a signed 128-bit conversion.

to_int128_or_null(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int128

Accepts integral values representable in int128, including negative magnitudes as low as -2^127. A failed parse, nonzero fractional component or a value below -2^127 or above 2^127-1 becomes NULL instead of a conversion exception. SQL NULL is retained, and incompatible logical argument types raise TYPE_MISMATCH.

Example

to_int128_or_null('170141183460469231731687303715884105728')
-- → NULL

to_int128_or_zero

Replaces failed wide signed-integer conversions with zero while preserving absent values.

to_int128_or_zero(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int128

Produces an exact int128 within -2^127 through 2^127-1, or zero if parsing, integrality or range checks fail. This includes positive 2^127, which is outside the signed domain despite fitting an unsigned 128-bit type. SQL NULL remains NULL, and incompatible input kinds raise TYPE_MISMATCH.

Example

to_int128_or_zero('170141183460469231731687303715884105728')
-- → 0

to_int16

Converts numeric input or an enum code into a signed integer between −32768 and 32767.

to_int16(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int16
to_int16(x:enum(E)) -> int16

Returns an int16 after checking that text or a numeric value represents an exact integer in the range −32768 through 32767; the enum overload returns its declared code. SQL NULL propagates, malformed text raises INVALID_ARGUMENT, and overflow or lossy narrowing raises NUMERIC_OVERFLOW. An incompatible source type raises TYPE_MISMATCH.

Example

to_int16('32767')
-- → 32767

to_int16_or_default

Uses an optional two-byte signed substitute when exact small-integer conversion cannot succeed.

to_int16_or_default(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int16
to_int16_or_default(x:string|bool|int*|uint*|float*|decimal(P,S),default:int16) -> int16

Attempts the same checked int16 conversion as to_int16, accepting only whole values from −32768 to 32767. A parse, range or precision failure selects the supplied int16 default, or zero when it is omitted; SQL NULL input remains NULL. A default of another type is rejected with TYPE_MISMATCH rather than converted implicitly.

Example

to_int16_or_default('40000', to_int16('-1'))
-- → -1

to_int16_or_null

Produces a nullable short integer, marking malformed or out-of-range values as missing.

to_int16_or_null(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int16

Parses text and checks numeric inputs against the signed 16-bit domain without truncating fractional values. Failed parsing, magnitude overflow and any lossy narrowing produce NULL instead of INVALID_ARGUMENT or NUMERIC_OVERFLOW, and an incoming SQL NULL also produces NULL. Incompatible argument types still raise TYPE_MISMATCH.

Example

to_int16_or_null('32768')
-- → NULL

to_int16_or_zero

Returns signed 16-bit zero for rejected numeric values while preserving valid small magnitudes.

to_int16_or_zero(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int16

Yields the exact int16 value on success and an int16 zero when parsing, precision or the −32768 to 32767 bound check fails. For example, a fractional value is replaced rather than rounded, and SQL NULL is retained rather than replaced. TYPE_MISMATCH still reports arguments outside the accepted scalar domains.

Example

to_int16_or_zero('not-an-integer')
-- → 0

to_int256

Builds an exact signed 256-bit integer from text, Boolean, decimal or floating-point input.

to_int256(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int256

Converts an accepted scalar to an integer in [−2^255, 2^255−1], with Boolean false and true mapping to zero and one. Text that is not a valid integer raises INVALID_ARGUMENT; an excessive magnitude or discarded fraction raises NUMERIC_OVERFLOW. SQL NULL stays NULL, and unlisted source types raise TYPE_MISMATCH.

Example

to_int256('170141183460469231731687303715884105728')
-- → 170141183460469231731687303715884105728

to_int256_or_default

Falls back to a supplied 256-bit signed value when a large-integer input is invalid.

to_int256_or_default(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int256
to_int256_or_default(x:string|bool|int*|uint*|float*|decimal(P,S),default:int256) -> int256

Checks whether the input can be represented exactly in the signed 256-bit range before producing an int256. Invalid digits, fractional loss or an out-of-range magnitude return the caller's int256 default, with zero used if no default is passed. NULL is preserved, while a mismatched default type raises TYPE_MISMATCH.

Example

to_int256_or_default('invalid', to_int256('-7'))
-- → -7

to_int256_or_null

Represents unsuccessful wide signed parsing as SQL NULL instead of a numeric exception.

to_int256_or_null(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int256

Returns an int256 only when text or a numeric argument has an exact whole-number representation between −2^255 and 2^255−1. NULL input and conversion failures all return NULL, including failures normally named INVALID_ARGUMENT or NUMERIC_OVERFLOW. The failure policy does not permit arrays or other incompatible sources, which raise TYPE_MISMATCH.

Example

to_int256_or_null('1.5')
-- → NULL

to_int256_or_zero

Replaces unrepresentable signed magnitudes with a 32-byte integer zero.

to_int256_or_zero(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int256

Successful conversion preserves every integer bit within the int256 bounds, including negative values down to −2^255. Malformed input, excessive magnitude or fractional loss instead yields a typed zero, but SQL NULL still yields NULL. Incompatible source descriptors are TYPE_MISMATCH errors rather than zero-producing conversion failures.

Example

to_int256_or_zero('broken')
-- → 0

to_int32

Checks and returns a whole number within the signed range −2147483648 through 2147483647.

to_int32(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int32

Produces an int32 from a supported textual, Boolean or numeric source without wrapping or truncating. Malformed integer text raises INVALID_ARGUMENT, and values outside the signed 32-bit bounds or requiring precision loss raise NUMERIC_OVERFLOW. NULL input returns NULL; a nonconvertible source type raises TYPE_MISMATCH.

Example

to_int32('-2147483648')
-- → -2147483648

to_int32_or_default

Substitutes a chosen ordinary signed integer for failed text parsing or narrowing.

to_int32_or_default(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int32
to_int32_or_default(x:string|bool|int*|uint*|float*|decimal(P,S),default:int32) -> int32

The result is an int32 when the source is an exact integer within [−2^31, 2^31−1]. Otherwise the optional int32 default is returned, or zero if absent, covering malformed text, overflow and fractional loss together. SQL NULL bypasses default and remains NULL; the default must already have the target type or TYPE_MISMATCH is raised.

Example

to_int32_or_default('2147483648', to_int32('-10'))
-- → -10

to_int32_or_null

Returns a four-byte signed result only for exact integers, using NULL for rejected inputs.

to_int32_or_null(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int32

Attempts conversion to int32, retaining both negative and positive integers inside its signed 32-bit limits. Parsing errors and NUMERIC_OVERFLOW conditions become NULL, so a value such as 2147483648 never wraps into a negative result. SQL NULL is also preserved, and incompatible argument types still raise TYPE_MISMATCH.

Example

to_int32_or_null('2147483648')
-- → NULL

to_int32_or_zero

Maps invalid signed 32-bit conversions to zero without changing an absent SQL value.

to_int32_or_zero(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int32

Converts exact integral input in the int32 range and returns a target-typed zero for malformed text, out-of-range values or precision loss. Both bounds are inclusive, so −2147483648 succeeds while 2147483648 receives the default. NULL remains NULL, and arguments not covered by the signature raise TYPE_MISMATCH.

Example

to_int32_or_zero('2147483648')
-- → 0

to_int64

Returns an exact signed eight-byte integer, rejecting values beyond the 64-bit limits.

to_int64(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int64

Checks a textual, Boolean or numeric source against −9223372036854775808 through 9223372036854775807 and returns int64. Fractional loss and exceeding either endpoint raise NUMERIC_OVERFLOW, while malformed integer text raises INVALID_ARGUMENT. SQL NULL propagates and incompatible input types produce TYPE_MISMATCH.

Example

to_int64('9223372036854775807')
-- → 9223372036854775807

to_int64_or_default

Recovers from bigint conversion failure using an optional signed 64-bit replacement.

to_int64_or_default(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int64
to_int64_or_default(x:string|bool|int*|uint*|float*|decimal(P,S),default:int64) -> int64

An exact whole-number value in [−2^63, 2^63−1] becomes int64; values that cannot be parsed or represented select the provided int64 replacement. Omitting that replacement chooses zero, while supplying SQL NULL as the source still returns NULL. An incorrectly typed replacement is a TYPE_MISMATCH error, not a reason to use zero.

Example

to_int64_or_default('bad', to_int64('-99'))
-- → -99

to_int64_or_null

Marks invalid bigint text and lossy eight-byte integer casts with a nullable result.

to_int64_or_null(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int64

Accepts valid integer text and numeric values only if they fit the signed 64-bit domain exactly. It turns INVALID_ARGUMENT and NUMERIC_OVERFLOW conversion failures into NULL and leaves existing SQL NULL unchanged. TYPE_MISMATCH remains an error for sources outside the listed scalar types.

Example

to_int64_or_null('9223372036854775808')
-- → NULL

to_int64_or_zero

Supplies bigint zero when signed 64-bit parsing cannot preserve the original numeric value.

to_int64_or_zero(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int64

Returns the int64 conversion when the value is integral and between −2^63 and 2^63−1. A text parse failure, an excessive magnitude or a fractional remainder produces zero rather than a truncated or wrapped value. SQL NULL stays NULL; incompatible argument types raise TYPE_MISMATCH.

Example

to_int64_or_zero('9223372036854775808')
-- → 0

to_int8

Converts a scalar into a signed byte with inclusive bounds of −128 and 127.

to_int8(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int8

Returns int8 only when an accepted text, Boolean or numeric input represents a whole number from −128 to 127. Parsing malformed text raises INVALID_ARGUMENT, and overflow or discarded fractional information raises NUMERIC_OVERFLOW. SQL NULL propagates; incompatible source types raise TYPE_MISMATCH.

Example

to_int8('-128')
-- → -128

to_int8_or_default

Uses a caller-selected tiny signed integer after an unsuccessful byte-range conversion.

to_int8_or_default(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int8
to_int8_or_default(x:string|bool|int*|uint*|float*|decimal(P,S),default:int8) -> int8

Attempts a checked int8 conversion, including the lower endpoint −128 and upper endpoint 127. Malformed text, fractional values and values beyond those endpoints return the optional int8 default, which is zero when omitted. SQL NULL remains NULL regardless of the default, and a default with another descriptor raises TYPE_MISMATCH.

Example

to_int8_or_default('128', to_int8('-1'))
-- → -1

to_int8_or_null

Returns a missing value when a scalar cannot fit exactly into a signed byte.

to_int8_or_null(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int8

Produces an int8 for exact integers between −128 and 127, or NULL when the source cannot be parsed or represented in that interval. The NULL result replaces INVALID_ARGUMENT and NUMERIC_OVERFLOW conversion failures, including fractional narrowing. Incoming SQL NULL remains NULL, but an incompatible argument type raises TYPE_MISMATCH.

Example

to_int8_or_null('128')
-- → NULL

to_int8_or_zero

Turns malformed or oversized tiny-integer input into a signed byte containing zero.

to_int8_or_zero(x:string|bool|int*|uint*|float*|decimal(P,S)) -> int8

Checks input against the complete int8 domain and emits its exact value when conversion succeeds. Conversion failures use zero, including numeric fractions and magnitudes below −128 or above 127, whereas SQL NULL continues to represent an absent value. Incompatible input types still cause TYPE_MISMATCH.

Example

to_int8_or_zero('-129')
-- → 0

to_ipv4

Constructs an IPv4 address from dotted text or a network-order 32-bit address value.

to_ipv4(x:string|uint32|ipv4) -> ipv4

Parses a dotted IPv4 string, interprets uint32 as a network-order address value, or preserves an existing ipv4 value. Invalid address text raises INVALID_ARGUMENT and incompatible source types raise TYPE_MISMATCH; SQL NULL returns NULL. The numeric value zero denotes 0.0.0.0.

Example

to_ipv4('192.0.2.10')
-- → 192.0.2.10

to_ipv4_or_default

Provides a chosen IPv4 default when address parsing fails, defaulting to 0.0.0.0.

to_ipv4_or_default(x:string|uint32|ipv4) -> ipv4
to_ipv4_or_default(x:string|uint32|ipv4,default:ipv4) -> ipv4

Converts dotted text or a network-order uint32 to ipv4, with existing ipv4 inputs retained. A conversion failure selects the supplied ipv4 default, or 0.0.0.0 if it is omitted, while SQL NULL bypasses that substitution. TYPE_MISMATCH rejects incompatible sources and default arguments that are not already ipv4.

Example

to_ipv4_or_default('bad-address', to_ipv4('192.0.2.1'))
-- → 192.0.2.1

to_ipv4_or_null

Produces a nullable four-octet address from accepted input and discards invalid address spellings.

to_ipv4_or_null(x:string|uint32|ipv4) -> ipv4

Returns the ipv4 value parsed from text or derived from a uint32 network-order address number. Invalid address syntax yields NULL rather than INVALID_ARGUMENT, and a SQL NULL source also remains NULL. The policy does not coerce incompatible source types, which raise TYPE_MISMATCH.

Example

to_ipv4_or_null('999.1.2.3')
-- → NULL

to_ipv4_or_zero

Returns the unspecified IPv4 address for text that cannot be decoded as a valid address.

to_ipv4_or_zero(x:string|uint32|ipv4) -> ipv4

Successful text, uint32 or ipv4 conversion returns the corresponding address; uint32 uses network-order significance. A failed conversion returns 0.0.0.0, not NULL, while an incoming SQL NULL remains NULL. Arguments outside the accepted source types raise TYPE_MISMATCH rather than selecting the unspecified address.

Example

to_ipv4_or_zero('invalid')
-- → 0.0.0.0

to_ipv6

Decodes a 128-bit IP address from text or network bytes, including mapped IPv4 input.

to_ipv6(x:string|fixed_string(16)|bytes|ipv4|ipv6) -> ipv6