SQL Enumerated Types

Enumerated (ENUM) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages. ENUM labels are case sensitive, by default; so ‘mon’ is not the same as ‘MON’. This can be overridden with the COLLATE column definition. See Collation below. The translations from interval ENUM values to textual labels are kept in the system catalog table, SYSTEM.FIELDS (see FIELDS System Table Description). An example of an ENUM type might be the days of the week, or a set of status values.

Declaration of Enumerated Types

ENUM types are specific to a TABLE object and are created using the CREATE TABLE command.

  1. An ENUM must be a quoted string literal and cannot be an expression.

  2. Duplicate values in an ENUM list will cause an error.

  3. Leading spaces in the ENUM column creation and in inserting values are significant, but trailing spaces are ignored. See Whitespace below.

  4. The empty string may be used as an ENUM value.

  5. ENUM values may be set to NULL.

For example:

CREATE TABLE tab_events ( id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
                          event_name STRING NOT NULL,
                          event_day ENUM ('Mon','Tue','Wed','Thu','Fri','Sat','Sun'),
                          status STRING CHECK (status in ('Active','Inactive') )
                         );

INSERT INTO tab_events (event_name, event_day,status)
    VALUES ('Boston Marathon','Mon','Active');

INSERT INTO tab_events (event_name, event_day, status)
   VALUES ('Boston Chowderfest', NULL, 'Inactive');

INSERT INTO tab_events (event_name, event_day, status)
   VALUES ('Boston POPs', '', 'Active');

SELECT * FROM tab_events;
 ID      EVENT_NAME     EVENT_DAY   STATUS
 --- ------------------ ---------- --------
  1  Boston Marathon         Mon   Active
  2  Boston Chowderfest   <null>   Inactive
  3  Boston POPs                   Active

Ordering

The ordering of the values stored for an ENUM type is the order in which the values were listed in the CREATE TABLE statement. Ordering is based on the positional integer value of the literal in the list. In the previous example, “Mon” has a value of 1, “Tue” has a value of 2, etc.
Empty strings are valid as an ENUM value and will have a positional value of 0. NULL ENUM values are treated as NULL (no positional value). These positional values can be used to query the ENUM.
Empty string ENUM values will sort as positional value 0 (i.e. first if sorting ascending and last if sorting descending), whereas NULL ENUM values will always sort first, regardless of ascending or descending sort order.
All standard comparison operators and related aggregate functions are supported for enums.

For example:

INSERT INTO tab_events (event_name, event_day,status)
    VALUES ('Red Sox vs Yankees','Sat','Active'),
           ('Bruins vs Rangers','Fri','Inactive'),
           ('Landmarks Orchestra concert','Wed','Active');

SELECT * FROM tab_events WHERE event_day > 'Wed';
 /* no result */

SELECT * FROM tab_events WHERE event_day > 3;
 ID      EVENT_NAME     EVENT_DAY   STATUS
 --- ------------------ ---------- --------
  4  Red Sox vs Yankees    Sat     Active
  5  Bruins vs Rangers     Fri     Inactive

SELECT * FROM tab_events WHERE event_day = 0;
 ID  EVENT_NAME  EVENT_DAY  STATUS
 --- ----------- ---------- -------
  3  Boston POPs            Active

SELECT * FROM tab_events WHERE event_day is null;
 ID      EVENT_NAME     EVENT_DAY   STATUS
 --- ------------------ ---------- --------
  2  Boston Chowderfest   <null>   Inactive

SELECT * FROM tab_events ORDER BY event_day;
 ID          EVENT_NAME          EVENT_DAY   STATUS
 --- --------------------------- ---------- --------
  2  Boston Chowderfest            <null>   Inactive
  3  Boston POPs                            Active
  1  Boston Marathon                Mon     Active
  6  Landmarks Orchestra concert    Wed     Active
  5  Bruins vs Rangers              Fri     Inactive
  4  Red Sox vs Yankees             Sat     Active

SELECT * FROM tab_events ORDER BY event_day desc;
 ID          EVENT_NAME          EVENT_DAY   STATUS
 --- --------------------------- ---------- --------
  2  Boston Chowderfest            <null>   Inactive
  4  Red Sox vs Yankees               Sat   Active
  5  Bruins vs Rangers                Fri   Inactive
  6  Landmarks Orchestra concert      Wed   Active
  1  Boston Marathon                  Mon   Active
  3  Boston POPs                            Active

Enumerated Value Comparison

ENUM column string values can be compared with other ENUM column string values, regardless of the position of the string value in the enumerated list. Using the same table in the previous examples, the “Mon” ENUM string value has a position value of 1. In the following table, the “Mon” ENUM string value has a position value of 2. Regardless, these two ENUM string values are equal.

CREATE TABLE tab_events_dup ( id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
                           event_name STRING NOT NULL,
                           event_day ENUM ('Sun','Mon','Tue','Wed','Thu','Fri','Sat'),
                           status STRING CHECK (status in ('Active','Inactive') )
                         );

INSERT INTO tab_events_dup (event_name, event_day,status)
    VALUES ('Boston Marathon','Mon','Active');

SELECT * FROM tab_events t1 , tab_events_dup t2
  WHERE t1.event_day = t2.event_day;

 ID    EVENT_NAME    EVENT_DAY  STATUS  ID    EVENT_NAME    EVENT_DAY  STATUS
 --- --------------- ---------- ------- --- --------------- ---------- -------
  1  Boston Marathon    Mon     Active   1  Boston Marathon    Mon     Active

Whitespace

Leading spaces in the ENUM column creation and in inserting values are significant, but trailing spaces are ignored.

For example:

CREATE TABLE tab_enum ( id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
                        enum_col ENUM (' leading_space','no_space','trailing_space ')
                      );

INSERT INTO tab_enum (enum_col) VALUES ('leading_space');
    /* ERROR:   string "leading_space" is out of range for enum */

INSERT INTO tab_enum (enum_col) VALUES ('trailing_space');

SELECT * FROM tab_enum WHERE enum_col = 'trailing_space' AND enum_col = 3;
 ID     ENUM_COL
 --- --------------
  1  trailing_space

INSERT INTO tab_enum (enum_col) VALUES ('no_space ');
INSERT INTO tab_enum (enum_col) VALUES (' no_space');
    /* ERROR:  string " no_space" is out of range for enum */

SELECT * FROM tab_enum;
 ID     ENUM_COL
 --- --------------
  1  trailing_space
  2        no_space

INSERT INTO tab_enum (enum_col) VALUES (' leading_space ');
SELECT * FROM tab_enum where enum_col = 1 and enum_col = ' leading_space';
 ID     ENUM_COL
 --- --------------
  3   leading_space

SELECT * FROM tab_enum where enum_col = 1 and enum_col = ' leading_space ';
 ID     ENUM_COL
 --- --------------
  3   leading_space

Collation

By default, ENUM labels are case sensitive. This can be overridden with the COLLATE column definition.

In the first example, the uppercase XYZ causes the entire INSERT statement to fail.
In the second and third examples, the COLLATE option overrides the case insensitive ENUM value. The inserted value is the same case as the ENUM value.

DROP TABLE IF EXISTS tab_enum;
CREATE TABLE tab_enum ( enum_col ENUM ('abc','def','xyz')
                      );

INSERT INTO tab_enum (enum_col) VALUES ('abc'),('XYZ');
    /* ERROR:   string "XYZ" is out of range for enum */

SELECT * FROM tab_enum;
/* No Records */


DROP TABLE IF EXISTS tab_enum;
CREATE TABLE tab_enum ( enum_col ENUM ('abc','def','xyz') COLLATE case_insensitive
                       );

INSERT INTO tab_enum VALUES ('abc'),('XYZ'),('xyz');
SELECT * FROM tab_enum;

 ENUM_COL
 ---------
    abc
    xyz
    xyz


DROP TABLE IF EXISTS tab_enum;
CREATE TABLE tab_enum ( enum_col ENUM ('abc','def','XYZ') COLLATE case_insensitive
                       );

INSERT INTO tab_enum VALUES ('abc'),('XYZ'),('xyz');
SELECT * FROM tab_enum;

 ENUM_COL
 ---------
    abc
    XYZ
    XYZ