Enumerated types

Declare an enum type

Use CREATE TYPE to define a new enum type:

CREATE TYPE <name> AS ENUM ('<value1>', '<value2>', ...);

For example:

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

Once defined, use the enum type in table definitions and function signatures just like any built-in type:

CREATE TABLE person (
    name         text,
    current_mood mood
);

INSERT INTO person VALUES ('Moe', 'happy');

SELECT * FROM person WHERE current_mood = 'happy';

Output:

 name | current_mood
------+--------------
 Moe  | happy
(1 row)

Ordering

The order of enum values is the order in which they were listed when the type was created. All standard comparison operators and aggregate functions — including MIN() and ORDER BY — work with enum values.

INSERT INTO person VALUES ('Larry', 'sad');
INSERT INTO person VALUES ('Curly', 'ok');

-- Filter using a comparison operator
SELECT * FROM person WHERE current_mood > 'sad';

Output:

 name  | current_mood
-------+--------------
 Moe   | happy
 Curly | ok
(2 rows)

-- Sort by enum order
SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;

Output:

 name  | current_mood
-------+--------------
 Curly | ok
 Moe   | happy
(2 rows)

-- Use an aggregate function
SELECT name FROM person
WHERE current_mood = (SELECT MIN(current_mood) FROM person);

Output:

 name
-------
 Larry
(1 row)

Type safety

Each enum type is isolated — values from different enum types cannot be compared directly.

CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
CREATE TABLE holidays (
    num_weeks integer,
    happiness happiness
);

INSERT INTO holidays(num_weeks, happiness) VALUES (4, 'happy');
INSERT INTO holidays(num_weeks, happiness) VALUES (6, 'very happy');
INSERT INTO holidays(num_weeks, happiness) VALUES (8, 'ecstatic');

-- Inserting a value not in the enum raises an error
INSERT INTO holidays(num_weeks, happiness) VALUES (2, 'sad');
-- ERROR: invalid input value for enum happiness: "sad"

-- Comparing across different enum types also raises an error
SELECT person.name, holidays.num_weeks FROM person, holidays
  WHERE person.current_mood = holidays.happiness;
-- ERROR: operator does not exist: mood = happiness

To compare values across enum types, cast both sides to text:

SELECT person.name, holidays.num_weeks FROM person, holidays
  WHERE person.current_mood::text = holidays.happiness::text;

Output:

 name | num_weeks
------+-----------
 Moe  |         4
(1 row)

Alternatively, write a custom operator or add explicit casts to your query.

Implementation details

• Case sensitivity: Enum labels are case sensitive. 'happy' and 'HAPPY' are distinct values. White space in labels is also significant.

• Adding and renaming values: There is support for adding new values to an existing enum type, and for renaming values. Existing values cannot be removed from an enum type, nor can the sort ordering of such values be changed without dropping and recreating the type.

• Storage size: Each enum value occupies 4 bytes on disk.

• Label length limit: The length of an enum label is limited by the NAMEDATALEN setting compiled into PostgreSQL. In standard builds, this is at most 63 bytes.

• System catalog: The mapping from internal enum values to their text labels is stored in the system catalog pg_enum. Query this catalog directly when you need to inspect or audit enum definitions.