Enumerated Types

ENUM is a string type created only when a table is created. A created ENUM list contains all optional enumerated values. Only values in the ENUM list are valid and can be in character string or index format.

The syntax for creating ENUM is as follows:

ENUM('val1','val2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

The character string and integer formats of an enumerated value have the same function. Take ENUM('beijing', 'shanghai', 'nanjing', 'wuhan') as an example. Table 1 shows the mapping between character strings and integers of enumerated values.

**Table 1** Mapping between strings and integers (indexes) of enumeration members
Character String	Integer Index
NULL	NULL
''	0
'beijing'	1
'shanghai'	2
'nanjing'	3
'wuhan'	4

The index of the NULL value is NULL.
In loose mode or when an invalid value is inserted using the INSERT IGNORE syntax, 0 is inserted, indicating an empty string. If an enumeration member is a valid empty string, the integer value is the position index of the empty string. If the empty string is invalid, the integer value is 0.

Table 2 lists the M-compatible ENUM types.

**Table 2** Enumerated types
Name	Description	Storage Space
ENUM	Enumerated type, which is used to store and operate optional enumerated values. Input formats: String, one of the optional enumeration members. Integer, one of the position indexes corresponding to the enumeration member. Output formats: String of an enumeration member when the enumeration is created. Value range: A maximum of 65535 enumeration members can be created. Strings or integers that represent enumeration members.	6 bytes

The input validity check of the ENUM type is affected by sql_mode. In loose mode, if an invalid value is inserted to a column of the ENUM type, 0 is inserted, indicating an empty string. In strict mode, if an invalid value is inserted, an error is reported.
Each member value of the ENUM type must be unique. If duplicate member values exist, an error is reported in strict mode. In loose mode, duplicate member values can be created, but the first value is used by default.
The ENUM data type supports the character set and collation features. Whether member values are duplicate is related to the collation feature of ENUM columns.
The ENUM data type cannot be used as a partition key.
An index of the ENUM type can be queried only by integer. The index cannot be queried by character string.
It is not recommended that the ENUM member value be a number because it may be confused with the index value.
Compared with other types, the ENUM type does not support index scan. You need to create a function expression index on a column of the ENUM type based on the data type to be queried to support index scan.

Examples

    
     
       
       
         -- Create a table and ENUM data.
m_db=# CREATE TABLE test_enum(c1 ENUM ('abc','efg','Chinese','Chinese character');
CREATE TABLE

-- View the table structure.
m_db=# select pg_get_tabledef('test_enum');
                                      pg_get_tabledef                                      
-------------------------------------------------------------------------------------------
 SET search_path = public;                                                                +
 CREATE TABLE test_enum (                                                                 +
     c1 ENUM('abc', 'efg', 'Chinese', 'Chinese character') CHARACTER SET `UTF8` COLLATE utf8mb4_general_ci+
 )                                                                                        +
 CHARACTER SET = "UTF8" COLLATE = "utf8mb4_general_ci"                                    +
 WITH (orientation=row, compression=no, storage_type=USTORE, segment=off);
(1 row)

-- Insert character string data.
m_db=# insert into test_enum values('abc');
INSERT 0 1
m_db=# insert into test_enum values('Chinese');
INSERT 0 1

-- Insert integer data.
m_db=# insert into test_enum values(2),(4);
INSERT 0 2

-- Query data in character string format.
m_db=# select * from test_enum where c1 = 'efg' or c1 = 'Chinese';
  c1  
------
 Chinese
 efg
(2 rows)

-- Query data in integer format.
m_db=# select * from test_enum where c1 = 1 or c1 = 4;
  c1  
------
 abc
 Chinese character
(2 rows)

-- Drop the table.
m_db=# drop table test_enum;
DROP TABLE

-- Create a table and ENUM data.
m_db=# create table t1(id int, c1 enum('a','bb','ccc'));
CREATE TABLE

-- Index scan is not supported when indexes of the ENUM type are directly created.
m_db=# create index idx_1 on t1(c1);
CREATE INDEX

m_db=# explain select c1 from t1 where c1 = 'a';
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on t1  (cost=0.00..59.00 rows=15 width=32)
   Filter: (cast_to_cstring(c1) = 'a'::text)
(2 rows)

-- Query strings. Create the cast_to_cstring function index to support index scan.
m_db=# create index id_t1 on t1((cast_to_cstring(c1)));
CREATE INDEX

m_db=# explain select c1 from t1 where c1 = 'a';
                             QUERY PLAN                             
--------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=4.30..13.78 rows=6 width=32)
   Recheck Cond: (cast_to_cstring(c1) = 'a'::text)
   ->  Bitmap Index Scan on id_t1  (cost=0.00..4.30 rows=6 width=0)
         Index Cond: (cast_to_cstring(c1) = 'a'::text)
(4 rows)

-- Query integers. Create the cast_to_int8 function index to support index scan.
m_db=# create index id_t2 on t1((cast_to_int8(c1)));
CREATE INDEX

m_db=# explain select c1 from t1 where c1 = 5;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=4.30..13.78 rows=6 width=32)
   Recheck Cond: (cast_to_int8(c1) = 5)
   ->  Bitmap Index Scan on id_t2  (cost=0.00..4.30 rows=6 width=0)
         Index Cond: (cast_to_int8(c1) = 5)
(4 rows)

        

      

    
   