Enumerated data type in ClickHouse

A ClickHouse table column of Enumerated data type can hold named values. We have to define the named values at the time of table creation. The main purpose is to efficiently store the columns with more unique elements.

The named values are required to be declared as Enum(‘string’ = 8/16-bit integer). (e.g Enum (‘apple’ = 1, ‘orange’ = 2)). We can also specify Enum8 or Enum16 based on the value type.

Creating a table with Enum type in one of the columns

CREATE TABLE enum_example
(col1 Enum8 ('a'=1, 'b'=2))
ENGINE TinyLog;

The table has only one column with Enum type and based on TinyLog engine. The column can store ‘a’/1, or ‘b’/2. Any attempts to store values other than these will result in exception.

Inserting the values

We can insert the values based on the names or values.

INSERT INTO enum_example VALUES ('a'), ('b'), ('a');

INSERT INTO enum_example VALUES (1), (2), (1);

Reading the values

chistadata :) SELECT * FROM enum_example;

SELECT *
FROM enum_example

Query id: 1ce72293-abff-498b-94f9-0d8c7ee0a0ab

┌─col1─┐
│ a    │
│ b    │
│ a    │
│ a    │
│ b    │
│ a    │
└──────┘

6 rows in set. Elapsed: 0.001 sec. 

Regardless of whether we insert the names or values, the names are displayed when we read the data from the table.

Let us try inserting bad values and see what happens.

chistadata :) INSERT INTO enum_example VALUES (3);

INSERT INTO enum_example FORMAT Values

Query id: e0af3405-3fb8-4058-85d1-657df92bf647

Ok.

1 row in set. Elapsed: 0.032 sec. 

chistadata :) SELECT * FROM enum_example

SELECT *
FROM enum_example

Query id: 2264d6b9-1eb7-4ad3-92e3-0de69280af09

Ok.
Exception on client:
Code: 36. DB::Exception: Code: 36. DB::Exception: Unexpected value 3 in enum. (BAD_ARGUMENTS) (version 22.12.1.1752 (official build)). (BAD_ARGUMENTS)

References:

https://clickhouse.com/docs/en/sql-reference/data-types/enum/#general-rules-and-usage