ClickHouse® data types

ClickHouse® supports a variety of data types to handle different kinds of data efficiently. This page gives an overview of the most common data types and when to use them.

How to get a list of supported data types

You can get a list of supported data types from the system.data_type_families table using the following statement:

SELECT name, case_insensitive, alias_to
FROM system.data_type_families
order by name

┌─name────────────────────────────┬─case_insensitive─┬─alias_to────┐
│ AggregateFunction               │                0 │             │
│ Array                           │                0 │             │
│ BIGINT                          │                1 │ Int64       │
│ BIGINT SIGNED                   │                1 │ Int64       │
│ BIGINT UNSIGNED                 │                1 │ UInt64      │
│ BINARY                          │                1 │ FixedString │
...

Numeric data types

  • Int8, Int16, ... Int256: Stores signed fixed-range integers from 8 to 256 bits wide.

  • UInt8, UInt16, ... UInt256: Stores unsigned fixed-range integers from 8 to 256 bits wide.

  • Float32, Float64: Stores floating point numbers. Rounding errors may occur when you perform calculation with floats. If high precision is needed, use decimals instead.

  • ️Decimal32(S), ... Decimal256(S): Stores fixed-point numbers where S is the number of digits to the right of the decimal point. Decimals keep precision in arithmetic operations, except for division where the least significant digits are discarded instead of rounding.

  • String (BINARY LARGE OBJECT, BLOB): Stores strings of any length that can contain null bytes. ClickHouse® doesn’t support encoding on the data type level, and strings are stored and output as-is. Encoding is applied at the access level with certain functions.

  • FixedString(N): Stores fixed-length strings of N bytes.

Date and time data types

  • Date: Stores dates as a number of days since January 1, 1970 (0). It’s unsigned and doesn’t support timezones. The supported range is [1970-01-01, 2149-06-06].

  • Date32: Stores dates as a number of days, with January 1, 1970 being 0. Negative numbers refer to the number of days before 1970. The supported range is [1900-01-01, 2299-12-31].

  • DateTime, DateTime([timezone]): Stores dates and times with a precision of one second. The time zone is optional and stored in the column metadata.

  • DateTime64, DateTime64(precision, [timezone]): Stores dates and times with a precision of one nanosecond. The time zone is optional. The precision is 10^(-precision) seconds where precision is an integer of [ 0 : 9 ]. Common values are 3 (milliseconds), 6 (microseconds), and 9 (nanoseconds).

Boolean data type

  • Bool: Stores Boolean values. Internally, ClickHouse® uses UInt8 values for them.

Composite data types

  • Array(T): Can contain several items of any type, including arrays, but all items should be of the same type. The first item has an index of 1.

  • Tuple(T1, T2, ...): Stores collections of elements with different types — strings, numbers, or arrays. Tuples are commonly used for related data, such as user information (name, last name, address, and phone number).

  • Map: Stores key-value pairs. Keys can be stings and numbers, while values can be of any type, including map and array. Values can be accessed using bracket notation.

  • Nested structure: Similar to a nested table. Column names and types of such a nested structure are also specified in a CREATE statement. Every table row corresponds to an arbitrary number of rows in a nested structure

  • Enum, Enum8, Enum16: Stores a set of fixed values used as an alternative to string values. Because the number of possible Enum values is limited, data is stored more efficiently compared to strings. ClickHouse® chooses the Enum type on data insertion, but you can specify a type if you want to have a specific value range.

Other data types

  • IPv4 and IPv6: Store IP addresses in 4 bytes as UInt32 and in 16 bytes as UInt128 big-endian respectively.

  • UUID: Stores UUID-compatible strings as an efficient representation of 128 bits.

Data field attributes

  • LowCardinality(T): Changes the data storage method to dictionary-encoded. Using LowCardinality for low-cardinal fields (ideally less than 10,000 distinct values) significantly improves the performance of SELECT queries.

    It shouldn’t be used with UInt8 values because dictionary addresses use more bytes than values themselves.

  • Nullable(T): Allows for NULL values to be used for missing values.

Previous
CREATE
Next
WebSQL