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 FLOAT or DOUBLE in SQL — they are equivalent.
INTEGER — Signed 64-bit integer. Write INTEGER, INT, or BIGINT in SQL — they are all equivalent.

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], or TIMESTAMP[d] to declare a specific scale — this matters when casting integer epoch columns.

INTERVAL — A duration or period of time. Written as INTERVAL 'value' UNIT where UNIT is one of DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, or MICROSECOND.

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.

VARBINARY — Raw binary data (arbitrary bytes). Use for hashes, encoded payloads, or any non-text binary content.

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).

VECTOR — A fixed-length vector of FP16 (half-precision) floating-point values. Used for similarity search and ML embedding workloads. Declared as VECTOR(n) where n is the number of dimensions.

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.

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.