Enum

Enumerated (enum) types are data types that are comprised of a static, predefined set of values with a specific order. They are equivalent to the enum types in a number of programming languages. An example of an enum type might be the days of the week, or a set of status values for a piece of data.

See the official documentation on enumerated types.

Declaration of Enumerated Types

Enum types are created using the CREATE TYPE command, for example:

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

Once created, the enum type can be used in table and function definitions much like any other type:

Example. Basic Enum Usage

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
   name text,
   current_mood mood
);

INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood
------+--------------
 Moe  | happy
(1 row)

Features

Ordering

The ordering of the values in an enum type is the order in which the values were listed when the type was declared. All standard comparison operators and related aggregate functions are supported for enums. For example:

Example. Enum Ordering

INSERT INTO person VALUES ('Larry', 'sad');
INSERT INTO person VALUES ('Curly', 'ok');
SELECT * FROM person WHERE current_mood > 'sad';
name  | current_mood 
-------+--------------
Moe   | happy
Curly | ok
(2 rows)

SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;
 name  | current_mood 
-------+--------------
 Curly | ok
 Moe   | happy
(2 rows)

SELECT name FROM person
 WHERE current_mood = (SELECT MIN(current_mood) FROM person);
 name  
-------
 Larry
(1 row)

Type Safety

Enumerated types are completely separate data types and may not be compared with each other.

Example. Lack of Casting

CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
CREATE TABLE holidays (                                           
   num_weeks int,
   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');
INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
ERROR:  invalid input value for enum happiness: "sad"
SELECT person.name, holidays.num_weeks FROM person, holidays
 WHERE person.current_mood = holidays.happiness;
ERROR:  operator does not exist: mood = happiness

If you really need to do something like that, you can either write a custom operator or add explicit casts to your query:

Example. Comparing Different Enums by Casting to Text

SELECT person.name, holidays.num_weeks FROM person, holidays
 WHERE person.current_mood::text = holidays.happiness::text;
 name | num_weeks 
------+-----------
 Moe  |         4
(1 row)

Implementation Details

An enum value occupies four bytes on disk. The length of an enum value's textual label is limited by the NAMEDATALEN setting compiled into PostgreSQL; in standard builds this means at most 63 bytes.

Enum labels are case sensitive, so 'happy' is not the same as 'HAPPY'. Spaces in the labels are significant, too.

An Alternative Way To Do The Same

Instead of using an enum type we can set up a CHECK CONSTRAINT - this tells postgresql to make sure that the value we are entering is valid.

CREATE TABLE person (
 personid int not null primary key,
 favourite_colour varchar(255) NOT NULL,
 CHECK (favourite_colour IN ('red', 'blue', 'yellow', 'purple'))
);

INSERT INTO person(personid, favourite_colour) VALUES (1, 'red');
INSERT 0 1

Now for something not in the list:

INSERT INTO person(personid, favourite_colour) VALUES (2, 'green');
ERROR: new row for relation "person" violates check constraint "person_favourite_colour_check"

FAQ

Why we can't remove an ENUM value

We can't remove a value, it may be used in many places : several tables, indexes...

See this answer by Tom Lane: https://www.postgresql.org/message-id/21012.1459434338%40sss.pgh.pa.us

No, it is NOT SAFE TO DO THAT. At least not unless you also drop or reindex every index on columns of the enum type. Even if you've deleted every occurrence of the target value appearing in table rows, and vacuumed away those rows so that their leaf index entries are gone, the target value could still exist in upper index pages (as a page boundary value, for example). Delete the pg_enum entry and you'll break the index, because enum_cmp() won't know what to do when visiting that index entry.

Concerns like this are exactly why there is no ALTER TYPE DELETE VALUE, and probably never will be. If you need a non-fixed set of key values, you're much better off using a foreign key instead of an enum type.