Enum

Name	Description
ENUM	Dictionary representing all possible string values of a column

The enum type represents a dictionary data structure with all possible unique values of a column. For example, a column storing the days of the week can be an enum holding all possible days. Enums are particularly interesting for string columns with low cardinality (i.e., fewer distinct values). This is because the column only stores a numerical reference to the string in the enum dictionary, resulting in immense savings in disk storage and faster query performance.

Creating Enums

You can create an enum using hardcoded values:

Query

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

You can create enums in a specific schema:

Query

CREATE SCHEMA my_schema;
CREATE TYPE my_schema.mood AS ENUM ('sad', 'ok', 'happy');

Anonymous enums can be created on the fly during casting:

Query

SELECT 'clubs'::ENUM ('spades', 'hearts', 'diamonds', 'clubs') AS suit;

Result

 suit------- clubs

You can also create an enum using a SELECT statement that returns a single column of VARCHARs. The set of values from the select statement will be deduplicated automatically, and NULL values will be ignored:

Query

CREATE TYPE region AS ENUM (SELECT region FROM sales_data);

If you are importing data from a file, you can create an enum for a VARCHAR column before importing:

Query

CREATE TYPE region AS ENUM (SELECT region FROM 'sales_data.csv');
CREATE TABLE sales_data (amount INTEGER, region region);
COPY sales_data FROM 'sales_data.csv';

Using Enums

Enum values are case-sensitive, so 'maltese' and 'Maltese' are considered different values:

Query

CREATE TYPE breed AS ENUM ('maltese', 'Maltese');
-- Will return falseSELECT 'maltese'::breed = 'Maltese'::breed;
-- Will errorSELECT 'MALTESE'::breed;

Result

 ?column?---------- f
error db error: ERROR: Could not convert string 'MALTESE' to UINT8

After an enum has been created, it can be used anywhere a standard built-in type is used. For example, we can create a table with a column that references the enum.

Query

CREATE TABLE person (    name TEXT,    current_mood mood);
INSERT INTO person VALUES    ('Pedro', 'happy'),    ('Mark', NULL),    ('Pagliacci', 'sad'),    ('Mr. Mackey', 'ok');

The following query will fail since the mood type does not have a quackity-quack value.

Query

INSERT INTO person VALUES ('Hannes', 'quackity-quack');

Result

error db error: ERROR: Could not convert string 'quackity-quack' to UINT8

Enums vs. Strings

SereneDB enums are automatically cast to VARCHAR types whenever necessary. This characteristic allows for comparisons between different enums, or an enum and a VARCHAR column.

It also allows for an enum to be used in any VARCHAR function. For example:

Query

SELECT current_mood, regexp_matches(current_mood, '.*a.*') AS contains_a FROM person;

Result

 current_mood | contains_a--------------+------------ happy        | t NULL         | NULL sad          | t ok           | f

When comparing two different enum types, SereneDB will cast both to strings and perform a string comparison:

Query

CREATE TYPE new_mood AS ENUM ('happy', 'anxious');
SELECT * FROM personWHERE current_mood = 'happy'::new_mood;

Result

 name  | current_mood-------+-------------- Pedro | happy

When comparing an enum to a VARCHAR, SereneDB will cast the enum to VARCHAR and perform a string comparison:

Query

SELECT * FROM personWHERE current_mood = name;

Result

name	current_mood

When comparing against a constant string, SereneDB will perform an optimization and try_cast(⟨constant string⟩, enum_type) so that physically we are doing an integer comparison instead of a string comparison (but logically it is still a string comparison):

Query

SELECT * FROM personWHERE current_mood = 'sad';

Result

 name      | current_mood-----------+-------------- Pagliacci | sad

Warning This means that comparing against a random (non-equivalent) string always results in false (and does not error):

Query

SELECT * FROM personWHERE current_mood = 'bogus';

Result

name	current_mood

If you want to enforce type-safety, cast to the enum explicitly:

Query

SELECT * FROM personWHERE current_mood = 'bogus'::mood;

Result

error db error: ERROR: Could not convert string 'bogus' to UINT8

Ordering of Enums

Enum values are ordered according to their order in the enum's definition. For example:

Query

CREATE TYPE priority AS ENUM ('low', 'medium', 'high');
SELECT 'low'::priority < 'high'::priority AS comp;

Result

 comp------ t

Query

SELECT unnest(['medium'::priority, 'high'::priority, 'low'::priority]) AS mORDER BY m;

Result

 m-------- low medium high

Attention

If you compare an enum to a non-enum (e.g., a VARCHAR or a different enum type), the enum will first be cast to a string (as described in the previous section), and the comparison will be done lexicographically as with strings:

Query

CREATE TABLE tasks (name TEXT, priority_level priority);
INSERT INTO tasks VALUES ('a', 'low'), ('b', 'medium'), ('c', 'high');
-- WARNING!-- Equivalent to `WHERE priority_level::VARCHAR >= 'medium'`SELECT * FROM tasksWHERE priority_level >= 'medium';

Result

 name | priority_level------+---------------- b    | medium c    | high

So, if you want to e.g. "get all priorities at or above medium" then explicitly cast to the enum type:

Query

SELECT * FROM tasksWHERE priority_level >= 'medium'::priority;

Result

 name | priority_level------+---------------- b    | medium c    | high

Functions

See Enum Functions.

For example, show the available values in the mood enum using the enum_range function:

Query

SELECT enum_range(NULL::mood) AS my_enum_range;

Result

 my_enum_range---------------- {sad,ok,happy}

Enum Removal

Enum types are stored in the catalog, and a catalog dependency is added to each table that uses them. It is possible to drop an enum from the catalog using the following command:

Query

DROP TYPE ⟨enum_name⟩;

Currently, it is possible to drop enums that are used in tables without affecting the tables.

Warning This behavior of the enum removal feature is subject to change. In future releases, it is expected that any dependent columns must be removed before dropping the enum, or the enum must be dropped with the additional CASCADE parameter.