Data Types
The following data types are supported by Opteryx. Click a name for details.
Numeric types
- DECIMAL — Exact fixed-point number with declared precision and scale:
DECIMAL(precision, scale). Precision is the total number of significant digits (1–38); scale is the number of digits after the decimal point (0–precision). For example,DECIMAL(10, 2)holds values up to 99999999.99. - FLOAT — 64-bit IEEE 754 double-precision floating-point number. Write
FLOATorFLOAT64in SQL — they are equivalent. - INTEGER — Signed 64-bit integer. Write
INTEGERorINT64in SQL — they are equivalent.
Temporal types
- DATE — A calendar date with no time component. Stored as the number of days since 1970-01-01.
- TIME — A time of day with no date component. Stored as microseconds since midnight (TIME64).
- TIMESTAMP — A date and time value. The default scale is microseconds. Use
TIMESTAMP[s],TIMESTAMP[ms],TIMESTAMP[us],TIMESTAMP[ns], orTIMESTAMP[d]to declare a specific scale — this matters when casting integer epoch columns.
Interval types
- INTERVAL — A duration or period of time. Written as
INTERVAL 'value' UNITwhere UNIT is one ofYEAR,MONTH,WEEK,DAY,HOUR,MINUTE,SECOND,MILLISECONDorMICROSECOND(plurals such asDAYSare accepted). A range such asINTERVAL '1 3' MONTH TO DAYtakes one value per unit, filled from the leading unit.
Text types
- NVARCHAR — A variable-length UTF-8 encoded text string. Use NVARCHAR for any text that may contain non-ASCII characters. JSON columns are stored as NVARCHAR.
- VARCHAR — A variable-length ASCII text string. Use VARCHAR for columns that contain only ASCII characters. For text with accented characters, emoji, or any non-ASCII content, use NVARCHAR instead.
Binary types
- VARBINARY — Raw binary data (arbitrary bytes). Use for hashes, encoded payloads, or any non-text binary content.
Boolean types
- BOOLEAN — A logical TRUE or FALSE value.
Collection types
- ARRAY — An ordered sequence of elements, all of the same type. Array columns appear when reading Parquet or JSONL files that contain repeated/array fields. The element type is declared as
ARRAY<type>(e.g.ARRAY<INTEGER>,ARRAY<VARCHAR>). - VARIANT — A semi-structured type produced exclusively by the
->operator when extracting a JSON field from a VARCHAR/NVARCHAR/VARBINARY column. Use->to extract a field as VARIANT (a JSON value), or->>to extract the same field as NVARCHAR (JSON strings unquoted to plain text).
Network types
- IPV4 — An IPv4 address. Stored as an unsigned 32-bit integer and displayed in dotted-decimal notation. Because the storage is numeric, ordering, grouping, joining and comparison all operate on the underlying integer — and unsigned integer order is exactly IPv4 address order.
Identifier types
- UUID — A universally unique identifier (RFC 9562). Stored as its 16 bytes in network order, so ordering, grouping, joining and comparison work on the bytes and byte order is the same as the order of the canonical text.
Null type
- NULL — The absence of a value. NULL is not a type you declare — it appears when a column has no value or an expression produces no result.