Skip to content
Coordinator DB
View as Markdown
View as Markdown

Variant ​

Variant stores one active value from a declared set of types. Each value retains its selected type, so text and numeric alternatives remain distinguishable.

Creating Variant ​

text
Variant(T1, T2, ...)

A Variant supports up to 254 distinct, nonnullable alternatives. Canonical type-name order determines their declaration order. Directly nested unions, nullable alternatives and similar numeric or calendar alternatives are rejected.

SQL NULL is accepted without a Nullable wrapper.

Conversion between Variant and other columns ​

A value whose concrete type is an admitted alternative keeps it. Otherwise numeric values try a numeric alternative first. Text is tried against non-string alternatives before falling back to String.

A text value that would lose a fraction when converted to an integer remains String when that alternative is available. For example, '123' can select UInt64, while '1.5' remains String.

Functions with Variant arguments ​

variant_type(value) returns the active type name, or 'None' for NULL. variant_element(value, 'Type') extracts that alternative. An admitted but inactive alternative returns NULL; requesting an undeclared alternative raises an error.

Extract a numeric alternative before arithmetic. Ordering compares the active type's canonical name and then the contained value; equal numbers in different active types remain distinct.

Examples ​

Inspecting the active type ​

sql
SELECT variant_type(CAST('123' AS Variant(UInt64, String))),
       variant_type(CAST('hello' AS Variant(UInt64, String))),
       variant_type(CAST('1.5' AS Variant(UInt64, String)))
FORMAT csv;
text
"UInt64","String","String"

Extracting an admitted alternative ​

sql
SELECT variant_element(CAST('123' AS Variant(UInt64, String)), 'UInt64') + 1,
       variant_element(CAST('hello' AS Variant(UInt64, String)), 'UInt64')
FORMAT csv;
text
124,\N

See Dynamic when the alternative types are not known in advance.