Enum8 and Enum16 ​

An Enum defines a fixed mapping between text labels and signed integer codes. Queries display the labels; comparison, grouping and sorting use the declared codes.

Use an Enum when the admitted labels form a known vocabulary, such as a job status. Use String when new labels should be accepted without a schema declaration.

Syntax ​

Enum8('label' = code, ...)
Enum16('label' = code, ...)
Enum('label', ...)

Labels are case-sensitive and must be unique. Codes must also be unique. Enum8 accepts codes from -128 through 127; Enum16 accepts -32768 through 32767. Enum selects the smallest supported width containing all codes.

Omitted codes are assigned in declaration order, beginning at 1; a code after an explicit code continues from that value.

General Rules and Usage ​

Writes accept declared labels or declared numeric codes. An unknown label or undeclared code is rejected. A numeric cast exposes the code; ordinary query output shows its label.

Two values from different Enum definitions compare equal only when both code and label agree. The type's implicit default is its lowest declared code. A column can still contain SQL NULL unless it declares NOT NULL.

Usage Examples ​

Job statuses ​

CREATE TABLE jobs
(
    id UInt32,
    status Enum8('pending' = 1, 'running' = 2, 'done' = 3) DEFAULT 'pending'
);
INSERT INTO jobs(id) VALUES (1);
INSERT INTO jobs VALUES (2, 'done');

SELECT id, status, CAST(status AS Int8) AS code
FROM jobs
ORDER BY status
FORMAT csv;

1,"pending",1
2,"done",3

Automatic numbering ​

SELECT CAST(CAST('medium' AS Enum('low', 'medium', 'high')) AS Int8)
FORMAT csv;

2