Skip to main content
Skip to main content
Edit this page

Variant(T1, T2, ...)

Beta feature. Learn more.

This type represents a union of other data types. Type Variant(T1, T2, ..., TN) means that each row of this type has a value of either type T1 or T2 or ... or TN or none of them (NULL value).

The order of nested types doesn't matter: Variant(T1, T2) = Variant(T2, T1). Nested types can be arbitrary types except Nullable(...), LowCardinality(Nullable(...)) and Variant(...) types.

Note

It's not recommended to use similar types as variants (for example different numeric types like Variant(UInt32, Int64) or different date types like Variant(Date, DateTime)), because working with values of such types can lead to ambiguity. By default, creating such Variant type will lead to an exception, but can be enabled using setting allow_suspicious_variant_types

Note

The Variant data type is a beta feature. To use it, set enable_variant_type = 1.

Creating Variant​

Using Variant type in table column definition:

Using CAST from ordinary columns:

Using functions if/multiIf when arguments don't have common type (setting use_variant_as_common_type should be enabled for it):

Using functions 'array/map' if array elements/map values don't have common type (setting use_variant_as_common_type should be enabled for it):

Reading Variant nested types as subcolumns​

Variant type supports reading a single nested type from a Variant column using the type name as a subcolumn. So, if you have column variant Variant(T1, T2, T3) you can read a subcolumn of type T2 using syntax variant.T2, this subcolumn will have type Nullable(T2) if T2 can be inside Nullable and T2 otherwise. This subcolumn will be the same size as original Variant column and will contain NULL values (or empty values if T2 cannot be inside Nullable) in all rows in which original Variant column doesn't have type T2.

Variant subcolumns can be also read using function variantElement(variant_column, type_name).

Examples:

To know what variant is stored in each row function variantType(variant_column) can be used. It returns Enum with variant type name for each row (or 'None' if row is NULL).

Example:

Conversion between a Variant column and other columns​

There are 4 possible conversions that can be performed with a column of type Variant.

Converting a String column to a Variant column​

Conversion from String to Variant is performed by parsing a value of Variant type from the string value:

Converting an ordinary column to a Variant column​

It is possible to convert an ordinary column with type T to a Variant column containing this type:

Note: converting from String type is always performed through parsing, if you need to convert String column to String variant of a Variant without parsing, you can do the following:

Converting a Variant column to an ordinary column​

It is possible to convert a Variant column to an ordinary column. In this case all nested variants will be converted to a destination type:

Converting a Variant to another Variant​

It is possible to convert a Variant column to another Variant column, but only if the destination Variant column contains all nested types from the original Variant:

Reading Variant type from the data​

All text formats (TSV, CSV, CustomSeparated, Values, JSONEachRow, etc) supports reading Variant type. During data parsing ClickHouse tries to insert value into most appropriate variant type.

Example:

Comparing values of Variant type​

Values of a Variant type can be compared only with values with the same Variant type.

The result of operator < for values v1 with underlying type T1 and v2 with underlying type T2 of a type Variant(..., T1, ... T2, ...) is defined as follows:

  • If T1 = T2 = T, the result will be v1.T < v2.T (underlying values will be compared).
  • If T1 != T2, the result will be T1 < T2 (type names will be compared).

Examples:

If you need to find the row with specific Variant value, you can do one of the following:

  • Cast value to the corresponding Variant type:
  • Compare Variant subcolumn with required type:

Sometimes it can be useful to make additional check on variant type as subcolumns with complex types like Array/Map/Tuple cannot be inside Nullable and will have default values instead of NULL on rows with different types:

Note: values of variants with different numeric types are considered as different variants and not compared between each other, their type names are compared instead.

Example:

Note by default Variant type is not allowed in GROUP BY/ORDER BY keys, if you want to use it consider its special comparison rule and enable allow_suspicious_types_in_group_by/allow_suspicious_types_in_order_by settings.

JSONExtract functions with Variant​

All JSONExtract* functions support Variant type: